INF: Index Tuning Wizard Best Practices

The Index Tuning Wizard (ITW) is a great resource for tuning a large number of queries with minimum effort.

The ITW will make some recommendations but will not necessarily recommend all indexes that might potentially be useful. ITW makes more and better recommendations on a database that is not tuned than on one that is well-tuned but has a couple of troublesome queries.

Best Practices

• Make sure that the wizard has a good, representative workload to work with. Depending on your business needs, your sample may have to include all queries against a particular database, or perhaps only those generated by a certain program, server, or batch job. You may have to sample throughout the day or only for a specific period.
  
  
• You can generate the workload by using SQL Profiler to trace to a file. Filter out information that you do not plan to tune, such as calls to SQLAgent or to non-relevant application databases on the server.
  
  
• The ITW can affect performance, so you need to run it on a test system or during off-peak hours. Avoid the use of the ITW on a production system during business hours.
  
  
• Before you run the ITW, update the statistics on your database. The more accurate the statistics are, the better the recommendations will be.
  
  
• If you run the ITW on a test system, make sure the tables, constraints, indexes, data selectivity, and number of rows are the same as those on your production system. This consistency increases the accuracy of the recommendations.
  
  
• Remove all index hints from the queries in the sample.
  
  
• By default, the disk on which the database resides limits the size and number of the indexes recommended. To change the settings that control the size and number of indexes, use the Advanced Options tab.
  
  
• To capture all events and data columns you need for a trace use the "SQLProfilerTSQL_Replay" template that is provided with SQL Server 2000. Refer to the Replaying Traces topic in SQL Server Books Online for more details.

Considerations

• The maximum number of queries that will be tuned from a single workload is 32,767. However, the default number of queries is much lower and you have to increase the maximum number of queries to tune when you start the Index Tuning Wizard.
  
  
• For the ITW to recommend indexes, there must be a significant amount of data in the tables being tuned.
  
  
• It is best if only one user tries to tune a database at a time.
  
  
• If a potential index only provides a small benefit over an existing index, the potential index is not included in the recommendations.
  
  
• Queries with optimizer hints are not tuned. However, any indexes specified in a hint are listed among the recommended indexes, even though other indexes on that table are not considered. Therefore, it is best to remove index hints before tuning queries.
• Refer to the Index Tuning Wizard topic in SQL Server Books Online for a list of items that can keep the ITW from recommending indexes. Some examples of what the ITW will not tune are:
  
  
• Primary key constraints and unique indexes.
• Cross-database queries.
• Queries with temporary tables or system tables.
• Tables with less than 10 pages allocated.

Common Issues with the Index Tuning Wizard

For additional information about the common issues you may experience with the Index Tuning Wizard, click the article numbers below to view the articles in the Microsoft Knowledge Base:

Additional Information

• If you close the ITW abnormally, you may have stranded hypothetical indexes that you have to manually remove. Use this code to find all hypothetical indexes left behind in a database:

  SELECT object_name(id), name AS Hypothetical FROM sysindexes
  WHERE indexproperty(id, name, 'IsHypothetical') = 1
  						

  For an example of how to drop these hypothetical indexes, see the sample code in the following Microsoft Knowledge Base article:
  293177  (http://support.microsoft.com/kb/293177/EN-US/ ) BUG: Hypothetical Clustered Index From Index Tuning Wizard
• If you have to schedule the ITW to run after hours, use the ITWiz utility that is documented in SQL Server Books Online.
  
  
• The ITW generates reports after it completes. Review those reports for details on what indexes the ITW considered and what indexes the ITW recommends.

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base: SQL Server Books Online; topics: "Index Tuning Wizard"; "Troubleshooting the Index Tuning Wizard"; "Viewing and Analyzing Traces"; "Saving Traces and Templates"

Index Tuning Wizard for Microsoft SQL Server 7.0 (http://msdn.microsoft.com/en-us/library/aa226167(SQL.70).aspx)

Index Tuning Wizard for Microsoft SQL Server 2000 (http://msdn.microsoft.com/en-us/library/aa902645(SQL.80).aspx)

Troubleshooting the Index Tuning Wizard (http://technet.microsoft.com/en-us/library/aa937579(sql.80).aspx)

Designing an Index (http://msdn.microsoft.com/en-us/library/aa933128.aspx)

Delaney, Kalen. Inside Microsoft SQL Server 2000. Microsoft Press, 2000. ISBN 0-7356-0998-5.

Soukup, Ron. Inside Microsoft SQL Server 7.0. Microsoft Press, 1999. ISBN 0-7356-0517-3.