The Index Tuning Wizard allows you to select and create an optimal set of indexes and statistics for a Microsoft® SQL Server™ 2000 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 an SQL script or a SQL 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 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. After 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:
A recommendation consists of SQL statements that can be executed to create new, more effective indexes and, if wanted, drop existing indexes that are ineffective. Indexed views are recommended on platforms that support their use. After the Index Tuning Wizard has suggested a recommendation, it can then be:
The Index Tuning Wizard does not recommend indexes on:
Other Index Tuning Wizard considerations include:
The Index Tuning Wizard may not make index suggestions if:
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:
The Index Tuning Wizard does not recommend that any indexes be dropped if the Keep all existing indexes option is selected. Only new indexes are 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.
The Index Tuning Wizard includes any index hint or query hint in the final recommendation, even if the index is not optimal for the table. Indexes on other tables referenced in the query may be proposed and recommended; however, all indexes specified as hints will always be part of the final recommendation. Hints can prevent the Index Tuning Wizard from choosing a better execution plan. Consider removing any index hint from queries before analyzing the workload.
Index Analysis in SQL 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 Query Analyzer.
To defer building the indexes recommended by Index Tuning Wizard, save the recommended SQL script using SQL 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 Tuning Wizard