The Index Tuning Wizard

The Index Tuning Wizard is a utility provided by SQL Server that can analyze a set of queries and make recommendations about the right mix of indexes or indexed views that could be defined to improve performance. You can also specify that the Index Tuning Wizard generate a SQL script to implement the index design that it recommends.

The Index Tuning Wizard does a great job of quickly analyzing a database and recommending a basic set of indexing options. Of course, it is not perfect, and it can make mistakes by not recommending an index that it should or by recommending a less than optimal index.

Although the recommended design might not be perfect, the Index Tuning Wizard provides an excellent starting point for any indexing strategy, especially if you are unfamiliar with the T-SQL code or access methods employed by an application. However, you should not rely completely on this tool because there will certainly be some instances in which you know an index should be applied and it is not recommended by the Index Tuning Wizard.

To use the Index Tuning Wizard, first create a workload in a trace file or table generated by SQL Profiler. The workload can then be specified as the input to the Index Tuning Wizard. The Index Tuning Wizard will analyze the workload and make index recommendations to improve query processing performance. If you don't want to go to the trouble of creating a workload using SQL Profiler, you can also choose a selection of one or more T-SQL statements in a Query Analyzer window as an input to the Index Tuning Wizard for index analysis. This is useful for tuning a troublesome query or batch process.

There are a number of ways to start up the Index Tuning Wizard:

  • Within SQL Profiler, select the Tools menu. From the Tools menu, select the Index Tuning Wizard menu option.

  • Within Enterprise Manager, select the Tools menu. From the Tools menu, select Wizards. In the Wizards dialog box, open up the Management list, click on Index Tuning Wizard, and click OK.

  • Within Query Analyzer, select the Query window and choose the Index Tuning Wizard option.

Each of these methods brings up the initial Index Tuning Wizard dialog box. To start using the Index Tuning Wizard, follow these steps:

  1. On the Index Tuning Wizard initial dialog box, click Next to continue. The Select Server and Database dialog box appears, as shown in Figure 34.1.

    Figure 34.1. The Select Server and Database dialog box.


  2. In the Select Server and Database dialog box, enter the name of the SQL Server and the database you want to tune. The Index Tuning Wizard considers only one database at a time. Choose the set of options you want the Index Tuning Wizard to apply when performing its analysis. Following are the available options:

    • Keep All Existing Indexes?If you check this option, the Index Tuning Wizard considers adding new indexes only. If you do not check this option, the Index Tuning Wizard considers dropping and redefining inefficient indexes. Allowing the wizard to redefine existing indexes offers the best chance for performance improvement but is also the most risky approach.

    • Add Indexed Views?This option is enabled only when connecting to an Enterprise or Developer Edition of SQL Server 2000. When this option is checked, the Index Tuning Wizard considers creating indexed views in addition to normal indexes. Indexed views will be considered only when specifying the Medium or Thorough tuning mode.

    • Tuning Mode?This option is used to specify how thoroughly you want the Index Tuning Wizard to analyze your database. For large workload files, the runtime could be significant if the thorough mode is specified. The Fast mode takes the least amount of time and generates a quick recommendation based on query analysis and limited interaction with the database. Thorough mode gives the highest quality set of recommendations, but also takes the longest time. The Medium mode balances thoroughness of the analysis with runtime, examining fewer possibilities than Thorough mode but generating a respectable set of recommendations.

    Click Next to continue. The Specify Workload dialog box appears, as shown in Figure 34.2.

    Figure 34.2. The Specify Workload dialog box.


  3. On the Specify Workload dialog box, you can choose from an existing workload file, a SQL Server trace table, or if you invoked the Index Tuning Wizard from within Query Analyzer, the currently selected SQL in the Query Analyzer window. For a workload file, you can choose from a previously saved SQL Server trace file or a SQL Script file containing queries you want to analyze.

    Also on this screen, the Advanced Options button brings up the index tuning parameters dialog box, as shown in Figure 34.3. This dialog box displays the data size, index size, and available space for the current database. In this screen, you can specify additional control over the tuning parameters to control the number of queries to analyze in the workload file, the maximum amount of space allowed for the recommended indexes, and the maximum number of columns allowed per index.

    Figure 34.3. The Index Tuning Parameters dialog box.


    If Limit Number of Workload Queries to Sample is unchecked, all queries in the workload file are analyzed. Click OK to save any changes made to the index tuning parameters and then click Next to bring up the Select Tables to Tune dialog box, as shown in Figure 34.4.

    Figure 34.4. The Select Tables to Tune dialog box.


  4. One of the new features in the Index Tuning Wizard in SQL Server 2000 is the capability to specify a projected number of rows for the tables you choose to analyze. The Index Tuning Wizard takes the projected number of rows into account when recommending indexes and indexed views.

    After you select the tables to analyze and specify any row projections, click Next to continue. Once the index analysis is complete, the Index Recommendations dialog box appears, as shown in Figure 34.5.

    Figure 34.5. The Index Recommendations dialog box.


  5. The Index Recommendations dialog box shows you all the indexes that have been recommended that can potentially improve query performance, as well as the indexes it recommends be dropped, if any. This dialog box shows indexes that it recommends even if they already exist. Remember, if you choose not to keep existing indexes, these indexes will be dropped and re-created when the generated index script is run.

    Clicking on the Analysis button brings up a list of reports generated by the Index Tuning Wizard, as shown in Figure 34.6.

    Figure 34.6. The Index Tuning Wizard Reports dialog box.


    These reports provide further analysis and information regarding the index recommendations. You can analyze the information in these reports to help you decide whether to accept or reject the index recommendations. All reports can be saved to files for easier analysis and historical purposes. The reports generated are summarized in Table 34.2.

    Table 34.2. Index Tuning Wizard Reports
    Report Description
    Index Usage Report (recommended or current) Provides information about the expected relative usage of the recommended or current indexes by the queries in the workload file and the estimated sizes of the indexes.
    Table Analysis Report Provides information about the top 100 most heavily used tables in the workload file along with the relative cost of all queries in which the table participates.
    View-Table Relations Report Provides information on the tables referenced by the recommended indexed views.
    Query-Index Relations Report (recommended or current) Reports which indexes are used by which queries in the workload file.
    Query Cost Report Provides the estimated reduction or increase in execution time for the 100 most expensive statements in the workload file.
    Workload Analysis Report Provides information about the frequency of execution of SELECT, INSERT, UPDATE, and DELETE queries in the workload file and their relative impact on the total cost of the workload.
    Tuning Summary Report Provides summary information about the analysis performed by the Index Tuning Wizard, including number of tables tuned, number of indexes and indexed views recommended to be created or dropped, number of queries examined, and the total time spent performing the analysis.

    When finished analyzing or saving the reports, click the Close button to return to the Index Recommendations dialog box. Click Next to continue. The Schedule Index Update Job dialog box appears, as shown in Figure 34.7.

    Figure 34.7. The Schedule Index Update Job dialog box.


  6. The Schedule Index Update Job dialog box allows you to make several choices about when and how to implement the index changes. You can choose to implement the changes immediately, schedule an execution, or create a script file that you can run manually at any later time. Click Next to continue. The Completing the Index Tuning Wizard dialog box appears. Click Finish on the Completing the Index Tuning Wizard dialog box to implement the index changes.

    If you choose to schedule the index creation, the Index Tuning Wizard creates a SQL Server Agent Job. Figure 34.8 shows an example of the T-SQL job step created.

    Figure 34.8. A T-SQL Job step generated by the Index Tuning Wizard when the Schedule Index Update option is chosen.


    You might prefer not to let the Index Tuning Wizard run or schedule the running of the index script, but save it to a file to run it manually. That way, you can review the script first and see more clearly the indexes and indexed views that the Index Tuning Wizard is recommending. Also, saving the script gives you the ability to apply the changes in multiple environments.

Be aware that the recommendations made by the Index Tuning Wizard depend on the quality of the workload file provided. The most important step in the process is choosing a workload file that is representative of the database systems usage. SQL Profiler provides an excellent means of capturing database activity over a period of time for a production or test environment so that you have a representative sample of actual database activity.


It is recommended that you run the Index Tuning Wizard on your databases periodically to analyze and re-evaluate your index design. In addition to the changes in data volume and data distribution, it is possible that your users could begin accessing the data differently or use different queries. This is especially true in data warehousing and ad hoc query environments. By continually sampling workload information, you can ensure that you are applying appropriate indexes based on current usage patterns.

Using the itwiz Command-Line Utility

If you want to automate and schedule the running of the Index Tuning Wizard instead of running it manually, you can use the itwiz command-line utility. You can set up a job in SQL Server Agent to invoke itwiz on a scheduled basis, or use an operating system scheduler. (For more information on scheduling jobs using SQL Server Agent, see Chapter 18, "SQL Server Scheduling and Notification.")

The syntax for itwiz is as follows:

itwiz -D database_name {-i workload_file | -t workload_trace_table_name} 
        -o generated_script_file_name
        [-S server_name[\instance]]
        {{-U login_id [-P password]} | ?E }
        [-f tuning_feature_set]
        [-K keep_existing_indexes]
        [-M recommendation_quality]
        [-B storage_bound]
        [-n number_of_queries]
        [-C max_columns_in_index]
        [-T table_list_file]
        [-m minimum_improvement_percent]

Table 34.3 describes the itwiz command-line options.

Table 34.3. itwiz Command-Line Options
Option Description
-D database_name Specifies the name of the database to which to connect and tune the indexes.
-i workload_file Specifies the path and filename of the workload file. The workload file can be a SQL Profiler trace file or a SQL script file.
-t [server].[database].[owner].workload_trace_table_name Specifies the location of a trace table that contains the workload trace.
-o generated_script_file_name Specifies the path and filename of the recommended index script file.
-S server_name[\instance] Specifies the name and optional instance of the SQL Server to which to connect. The default is the local server.
-U login_id Specifies the SQL Server login ID to use to connect to SQL Server.
-P password Specifies the password for the SQL Server login ID used to connect to SQL Server.
?E Specifies that a trusted connection is to be used to connect to SQL Server.
-f tuning_feature_setting

Specifies the features to be used when tuning the indexes. The values that can be specified are as follows:

0? All features (the default)

1? Indexes only

2? Indexed views only

-K keep_existing_indexes

Specifies whether the existing indexes can be dropped. The values that can be specified are as follows:

0? Do not keep existing indexes

1? Keep all existing indexes (the default)

-M quality_of recommendation

Specifies the runtime versus recommendation quality tradeoff. The values that can be specified are as follows:

0? Fast Mode

1? Medium Mode (the default)

2? Thorough Mode

-B max_index_space Specifies the maximum space to be used for the recommended set of indexes.
-n number_of_queries Specifies the number of queries to be tuned. The default is 200.
-C max_columns_in_index Specifies the maximum number of columns allowed in the recommended indexes.
-T table_list_file

Specifies the path and filename of a file that contains a list of tables to be tuned. You can also specify a row projection for each table. The format of the file is as follows:

[owner.]table [projected_number_of_rows]
[owner.]table [projected_number_of_rows]
-m minimum_improvement_percent Specifies the minimum performance improvement percentage for the workload file that must be met for itwiz to output an index recommendation.
-F Specifies that itwiz can overwrite an existing output file.
-v Enables itwiz to output verbose messages to the screen during execution.

The following is a sample execution of the itwiz command-line utility:

itwiz ?D bigpubs2000 ?i bigpubs_20010901.trc ?M 2 ?K 0 ?E ?o bigpubs_itwiz.sql 

This example specifies that itwiz should perform a thorough analysis, allowing the dropping of existing indexes. It will connect to the local server using a trusted connection and perform the analysis on the bigpubs2000 database. It uses a SQL Profiler trace file as input and outputs the index recommendation script to a file called bigpubs_itwiz.sql in the current directory.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features