Index Tuning Wizard

Creating and Maintaining Databases

previous page next page
Creating and Maintaining Databases

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™ 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:

  • 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 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:

  • 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.

Other Index Tuning Wizard considerations include:

  • 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 cannot be used to select or create indexes and statistics in databases on SQL Server version 6.5 or earlier.

  • The Index Tuning Wizard does not give an error when saving an SQL Script to a disk with insufficient available space.

  • The Index Tuning Wizard can consume significant CPU and memory resources during analysis. It is recommended that tuning should be performed against a test version of the production server, rather than the production server. Additionally, the wizard should be run on a separate computer from the computer running an instance of SQL Server.

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 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.

Using Index Tuning Wizard in SQL Query Analyzer

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

previous page start next page