Tricks for using the SQL Server Index Tuning Wizard

The SQL Server Index Tuning Wizard will recommend indexes for you to use based on a given workload, which you can specify. But remember that it is important to understand basic database indexing when designing and reviewing the Index Tuning Wizard's recommendations. The final answer in your environment will vary based on the database design, business processes, level of concurrency, data types, etc., and should be thoroughly tested. Do not be afraid to develop and test unconventional options based on the unique characteristics of your application to ensure the indexes you provide do not improve one area and cripple another.

The following list of tricks will help you use the

    Requires Free Membership to View

Index Tuning Wizard more effectively to improve performance in SQL Server 2000.

1. Index Tuning Wizard is one of those hidden gems in Enterprise Manager. To access the tool, open Enterprise Manager, navigate to the Tools menu and select the Wizard option. Once the Select Wizard interface pops up, expand the Management heading and double click on the Index Tuning Wizard option. The Index Tuning Wizard has the following interfaces:

  • Welcome Screen
  • Select Server and Database
  • Specify Workload
  • Select Tables to Tune
  • Index Recommendations
  • Schedule Index Update Job
  • Completing the Index Tuning Wizard

2. SQL Profiler is the key to Index Tuning Wizard's success. Running the wizard and getting recommendations on index design can be a big win for everyone. But, I have also seen great disappointment when the wizard doesn't recommend any indexes. A good run with SQL Profiler makes the difference. If the data provided to the Index Tuning Wizard is not representative of your issues, then the Index Tuning Wizard will not be able to provide valuable insight into the index selection. So, make sure that SQL Profiler captures the performance data you're interested in.

3. Capture Profiler sessions at different times during the day, and store the results of these sessions in separate tables. If you then move the data to a single table, you can see results from the Index Tuning Wizard as it analyzes the independent sets of data and the single set of data. The differences can offers insights that will help you decide which recommended indexes to implement.

4. Coordinate a single user's session with the business application in a controlled manner with SQL Profiler capturing data for this single session. Then use the Index Tuning Wizard to analyze and recommend transactions in an isolated manner. Next, check the query plans to determine which indexes are used and validate that none of the queries are table scanning as opposed to using an index.

5. You can call the Index Tuning Wizard directly from Query Analyzer by highlighting the query and pressing CTRL + I. Then follow the Wizard's steps to complete the analysis. The value of launching the Index Tuning Wizard directly from Query Analyzer is that you can analyze a single query independent of the remainder of the application code.

6. Review the recommendations that the Index Tuning Wizard offers to ensure they make sense for your environment. Do not just blindly add the indexes. Apply the indexes in a development environment and in a test environment and test with the same SQL Profiler data to see if the results are as expected.

7. Do not be afraid to add the indexes, but also revisit some basic database design and coding best practices. At times, an index can really do the trick. But, at other times, you must revisit the database design and coding for a final resolution.

The art and science of SQL Server indexing

 Home: Introduction
 Part 1: Indexing Q&A
 Part 2: SQL Server 2000 indexing dos and don'ts
 Part 3: Tricks for using the Index Tuning Wizard

Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server.

This was first published in February 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.