Tuning and optimizing data access

Posted on

These questions are based on: 70-229 – Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition Microsoft Self-Test Software Practice Test.


Objective: Tuning and optimizing data access
SubObjective: Improve index use by using the Index Tuning Wizard


Item No. 70-
Single Answer, Multiple Choice


You are a SQL Server 2000 database developer for your company. You are developing a custom application that relies mostly on stored procedures. Before the application has been implemented in the production environment, you want to test it in a lab to determine whether the queries performed in the stored procedures are adequate.


Which of the following should you do?



  1. Enable the auto create statistics and auto update statistics database options.
  2. Use the Index Tuning Wizard.
  3. Create composite nonclustered indexes on the columns that are frequently referenced by the procedures.
  4. Set the transaction isolation level to REPEATABLE READ.





  1. Create composite nonclustered indexes on the columns that are frequently referenced by the procedures.


From the available choices, only using the Index Tuning Wizard provides a mechanism that allows analysis of the behavior of stored procedures. You should simulate the production environment in a test lab and create a sample workload file or trace for the Index Tuning Wizard, which will analyze the queries used in the workload against a specified database and generate recommendations about which indexes in the database should be created or dropped. It will also generate a report that shows the percentage of queries in which each of the existing indexes has been used. You can use this information to create the appropriate additional indexes that will optimize the performance of the stored procedures.


All other choices in this scenario suggest steps that typically might be taken after the relevant monitoring has been performed.


1. MCSE Training Kit Microsoft SQL Server 2000 Database Design and Implementation – SQL Server Monitoring and Tuning
– Lesson 2: Index Tuning and Database Partitioning

Like what you see? Share it.Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone


Posted in Archive|