SQL Server Index Tuning Wizard Tips

The Index Tuning Wizard is a powerful tool designed to help you identify existing indexes that aren’t being used, along with recommending new indexes that can be used to help speed up queries. It uses the actual queries you are running in your database, so its recommendations are based on how your database is really being used. The queries it needs for analysis come from the SQL Server Profiler traces you create. [7.0, 2000] Updated 3-15-2005

*****

Use the SQL Server Index Wizard and an appropriate Profiler trace file to help identify potential indexed views (SQL Server 2000 Enterprise is required). When the Index Wizard runs, it automatically looks for potential indexed views and recommends any that if finds. But don't rely on this tool as the only way to identify indexed views, as it is unable to identify all possible candidates. [2000] Updated 3-15-2005

*****

If you don't need the full power of the Index Tuning Wizard, but still want assistance when creating indexes for a table, you can use the "Perform Index Analysis" option located under the "Query" menu of the Query Analyzer in SQL Server 7. For SQL Server 2000, select "Index Tuning Wizard" from the "Query" menu of the Query Analyzer.

Instead of using a Profiler trace file to perform the analysis, it uses the query found in the Query window. While not as comprehensive an analysis as you get from using the Index Tuning Wizard, it is a good starting point when analyzing the performance of specific queries in your database. [7.0, 2000] Updated 2-11-2005

*****

When creating the Profiler trace that is used to base the Index Tuning Wizard index analysis on, select a time of day that is representative of typical transactions run in your SQL Server application. Since the Index Tuning Wizard bases its recommendations on actual queries, you want the queries in your trace file to be truly representative of how your users use your application.  [7.0, 2000] Updated 2-11-2005

*****

The longer you run the Profiler trace, the more accurate the Index Tuning Wizard recommendations will be because you will capture more of the types of typical queries run by your application. But keep in mind that there is an upper limit of 32,767 queries the Index Tuning Wizard can analyze at one time, and that the longer the trace, the longer the analysis will take (could take hours). [7.0, 2000] Updated 2-11-2005

*****

If you find that the Index Tuning Wizard takes more time to run than you have, consider setting the "Maximum columns to per index", under the "Advanced" settings, from the default of 16 columns to a smaller number. This reduces the number of possible indexes the Index Tuning Wizard will evaluate, saving some time, although not a lot. In most cases, especially for OLTP applications, you won't want an index with a lot of columns in it. [7.0, 2000] Updated 2-11-2005

*****

Don't capture more in your Profiler trace than you need. For example, only collect data for a single database, not all of the databases on your server. Also, don't collect more events or data columns that you need to use the Index Wizard. The only events and data columns required by the Index Wizard include the SQL:BatchCompleted and the RPC:Completed events in the TSQL category, and the EventClass and Text data columns. The fewer the events and data columns you capture, the smaller the load Profiler puts on your server when collecting data. [7.0, 2000] Updated 2-11-2005

*****

When creating a trace for the Index Tuning Wizard, consider using the "Create Trace Wizard" to create a "Find the worst performing queries" trace. You can set this trace to only capture queries that run longer than a specified amount of time, such as 1000 milliseconds. Generally, I like to set it at 5 milliseconds, and let it run all day (periodically). Then I use this trace to feed into the Index Tuning Wizard.

My goal here, of course, is to limit the number of queries being tuned to those that are performing the worst. When doing this, be sure you don't have the Index Tuning Wizard evaluate potential indexes to be dropped. This is because you are not collecting performance data on every query, only the slow ones. If you do evaluate for dropping indexes, you might drop an index that is needed because the queries that use it run faster than the time specified above. [7.0]