Index Tuning Wizard

The Index Tuning Wizard allows you to select and create an optimal set of indexes and statistics for a Microsoft® SQL Server™ database without requiring an expert understanding of the structure of the database, the workload, or the internals of SQL Server.

To build a recommendation of the optimal set of indexes that should be in place, the wizard requires a workload. A workload consists of a SQL script or a SQL Server Profiler trace saved to a file or table containing SQL batch or remote procedure call (RPC) event classes and the Event Class and Text data columns. For more information, see TSQL Event Category.

If you do not have an existing workload for the Index Tuning Wizard to analyze, you can create one using SQL Server Profiler. Either create a workload using the Sample 1 - TSQL trace definition or create a new trace that captures the default events and data columns. Once you have determined that the trace has captured a representative sample of the normal database activity, the wizard can analyze the workload and recommend an index configuration that will improve the performance of the database.

The Index Tuning Wizard can:

Recommend the best mix of indexes for a database given a workload, by using the query optimizer to analyze the queries in the workload.
Analyze the effects of the proposed changes, including index usage, distribution of queries among tables, and performance of queries in the workload.
Recommend ways to tune the database for a small set of problem queries.
Allow you to customize the recommendation by specifying advanced options such as disk space constraints.

A recommendation consists of SQL statements that can be executed to create new, more effective indexes and, if desired, drop existing indexes that are ineffective. Once the Index Tuning Wizard has suggested a recommendation, it can then be:

Implemented immediately.
Scheduled to be implemented later by creating a SQL Server job that executes an SQL script.
Saved to an SQL script, to be executed manually by the user at a later time or on a different server.

Considerations

The Index Tuning Wizard does not recommend indexes on:

Tables referenced by cross-database queries that do not exist in the currently selected database.
System tables.
PRIMARY KEY constraints and unique indexes.

The Index Tuning Wizard is limited to a maximum of 32,767 tunable queries in a workload. Additional queries in the workload will not be considered. Additionally, queries with quoted identifiers are not considered for tuning.

The Index Tuning Wizard gathers statistics by sampling the data. Consequently, successive executions of the wizard on the same workload may result in variations in the indexes recommended as well as the improvements that result from implementing the recommendation.

The Index Tuning Wizard may not make index suggestions if:

There is not enough data in the tables being sampled.
The suggested indexes do not offer enough projected improvement in query performance over existing indexes.

The queries in the workload are analyzed in the security context of the user who invokes the Index Tuning Wizard. The user must be a member of the sysadmin fixed server role.

To reduce the execution time of the Index Tuning Wizard:
Ensure that Perform thorough analysis is not selected in the Select Server and Database dialog box. Performing a thorough analysis causes the Index Tuning Wizard to perform an exhaustive analysis of the queries, resulting in a longer execution time. However, selecting this option can result in a greater overall improvement in the performance of the tuned workload.
Tune only a subset of the tables in the database.
Reduce the size of the workload file.

The Index Tuning Wizard will not recommend that any indexes should be dropped if the Keep all existing indexes option is selected; only new indexes will be recommended, if appropriate. Clearing this option can result in a greater overall improvement in the performance of the workload. Additionally, the Index Tuning Wizard does not recommend dropping indexes on PRIMARY KEY constraints or UNIQUE indexes. However, it may drop or replace a clustered index that is not unique or currently created on a PRIMARY KEY constraint.

Note When you use the Index Tuning Wizard to analyze an SQL script that does not have a file extension of .sql, such as my_script.txt, an error ("Not a valid File Format") will be generated if you open the file with File Format set to Auto. Set File Format to ANSI SQL or UNICODE SQL instead.

Using Index Analysis in SQL Server Query Analyzer

Index Analysis in SQL Server Query Analyzer allows a single query or batch to be analyzed and a recommendation generated for the optimal set of indexes that should be in place to support the given query or batch. Only members of the sysadmin fixed server role can perform Index Analysis using SQL Server Query Analyzer.

Index Analysis does not recommend creating clustered indexes when tuning a single query or batch, whereas the Index Tuning Wizard may recommend one or more clustered indexes, if appropriate.

To defer building the indexes recommended by Index Analysis, save the recommended SQL script using SQL Server Query Analyzer. Saving the SQL script to a file allows the Transact-SQL statements recommended by Index Analysis to be examined before being executed. The SQL script can then be edited before being executed (for example, the names of the generated indexes can be changed).

To start the Index Tuning Wizard

To analyze a query using Index Analysis