Using the Index Tuning Wizard
The Microsoft SQL Server Index Tuning Wizard is one of the easiest and most effective mechanisms for improving query performance.

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.

If you do not have an existing workload for the Index Tuning Wizard to analyze, you can create one using SQL Profiler.  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 Query Execution Plan
Every query gets compiled by the database engine into an execution plan. The execution plan is the strategy that SQL Server will use to access the data. The access strategy (execution plan) considers many things including the query, indexes and data distributions. Optimization is performed each time SQL Server is required to create an execution plan. The Query Optimizer determines the best way to execute a query based on such things as:
1. The Query
2. Available indexes
3. Database table structure
4. Data (statistical data on the distribution of values)

Performance Tuning and Optimization of SQL Server oftens focuses on the execution plan for critical data used to optimize the query.
Another way to view a query plan selected by the Query Optimizer, without also returning a result set from executing a query, is by enabling the SET SHOWPLAN_TEXT or SET SHOWPLAN_ALL commands.



Using the workload and the execution plans built by the Query Optimizer, the Index Tuning wizard:

  • Recommends the best mix of indexes for a database and ways to tune the database for a set of problem queries.
  • Analyzes the effects of the proposed changes, including index usage, distribution of queries among tables, and performance of queries in the workload.

You can customize the recommendations by specifying advanced options such as how much disk space can be used by new indexes. A recommendation consists of SQL statements that, when executed, create more effective indexes and optionally drop existing indexes that are inefficient or unnecessary.

Index Tuning wizard recommendations are applied immediately, scheduled for later application, or saved to a script for manual execution.

Index Tuning wizard recommendations not include index creation for tables referenced by cross-database queries and system tables.

Tables created with PRIMARY KEY constraints and unique keys automatically generate indexes for these keys. Therefore, the Index Tuning wizard does not recommend indexes for tables containing these attributes, but it can drop or replace a clustered index that is not unique or is not created on a PRIMARY KEY constraint. There are other limits associated with the Index Tuning wizard. For more information, see SQL Server Books Online.

Running the Index Tuning Wizard
You can start the Index Tuning wizard from Enterprise Manager, Query Analyzer, or SQL Profiler.

  • In Enterprise Manager, the Index Tuning wizard is a listed wizard in the Select wizard window.
  • In Query Analyzer, the Index Tuning wizard is an option in the Query men.
  • in SQL Profiler it's an option in the Tools menu.

After connecting to the server, the Index Tuning wizard requires that you select a database and specify whether you want to keep the existing indexes, whether you want to create indexed views, and how thorough of an analysis should be performed. The wizard does not recommend that any indexes be dropped if the Keep All Existing Indexes checkbox is selected. Recommendations will include only new indexes.

If you are running SQL Server 2000 Enterprise Edition or Developer Edition, the Index Tuning wizard can create indexes on views if the Add Indexed Views checkbox is selected. The more thorough the analysis, the more significant will be the CPU consumption while analysis is being performed.

After you select the Index Tuning wizard configuration, you must select a workload. Workload data comes from a trace file or trace table or a selection in the Query Analyzer. The Query Analyzer selection option is available only if you start the Index Tuning wizard from the

Query Analyzer.
After you select the workload, you can change the default index tuning parameters, select the tables for the wizard to analyze, and then run the analysis. Following the analysis, the wizard might not make index suggestions if there isn't enough data in the tables being sampled or if recommending indexes do not offer enough projected improvement in query performance over existing indexes.