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 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
- The analysis will be conducted once the OK button is pressed
- 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: Answers to common indexing questions
Part 2: SQL Server 2000 indexing dos and don'ts
Part 3: Tricks for using the Index Tuning Wizard
Jeremy Kadlec
