Index Tuning Wizard - Page 2

Once you have selected your options then click the "Next>" button. Doing this brings up the following screen:

This screen allows you to identify the workload information that will be analyzed by the index tuning wizard. As you can see, I am using Query Analyzer to identify my workload. If your workload file contains more than 200 T-SQL commands, you want to limit the number of columns defined on an index, or limit the space that will be consumed by suggested indexes, then you should click on the "Advanced Options" button. On the advanced options page you can override the default index tuning wizard parameters. Once you have identified your workload file, and set the tuning parameters click the "Next>" button to bring up the screen where you can select the tables you want the wizard to analyze.

On this screen, you can select which tables will be analyzed by the index tuning wizard. I will be selecting all tables. Once you have selected the tables you are interested in having the index tuning wizard analyze, then click on the "Next>" button. Doing this will start the index tuning wizard analysis process. Once the wizard has performed its index tuning analysis, the "Index Recommendations" screen will be displayed. Here is the screen that was displayed for the analysis of my workload file:

On this screen shot, you can see that the index tuning wizard determined that it could improve performance by 22% based on my sample workload. This performance gain will be obtained by creating two new indexes. On the above screen, you will notice there is an "Analysis..." button. If you click on this button, the following screen will be displayed:

As you can see from this screen shot, the report being shown is the "Index Usage Report (Recommended Configuration)". This report is useful for identifying the usage and size of each index on the tables you selected to analyze. If your workload sample represents a good cross section of commands for a specific database, then you could use the "Percent usage" column to determine those indexes that were not used and therefore possibly not needed.

In addition to the report above, there are eight additional reports. Use the pulldown on the "Reports:" item to select a different report. Here is a list of the additional reports that are available:

• Index Usage Report (Current Configuration)
• Table Analysis Report
• View – Table Relations Report
• Query – Index Relations Report (Recommended Configuration)
• Query – Index Relations Report (Current Configuration)
• Query Cost Report
• Workload Analysis Report
• Tuning Summary Report

As you can see, the index tuning wizard is rich in reports. These reports are useful in helping you determine index utilization, and size. Each of these reports can be saved by using the "Save" button. When you have finished reviewing and saving any reports you desire, then click on the "Close" button, which will return you to the "Index Recommendations" screen above. To get to the screen below you click the "Next>" button on the "Index Recommendations" screen:

On this screen, you have the option of applying the recommended changes now, scheduling them at a later date and time or saving them to a script file. This is basically the last step of the index tuning wizard. Clicking "Next>" on this screen will take you to the "Competing The Index Tuning Wizard" screen where you can click "Finish" to complete the index tuning wizard process.

Command Line Execution of the Index Tuning Wizard

The index tuning wizard can be run from the command line by using the "itwiz" utility. Here is the command syntax for the index tuning wizard command line interface:

itwiz
    [-?] |
    [
        -D database_name {-i workload_file | -t workload_trace_table_name}
        -o 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]
        [-F][-v]
    ]

For additional information regarding each of these arguments, please refer to books online. The "itwiz" utility can produce a script for creating indexes just like the GUI version of the index tuning wizard, although the command line utility does lack the option of producing index tuning reports.

Considerations When running the Index Tuning Wizard

Below is a list of things you should consider when using the index tuning wizard to tune a workload file:

• The index tuning wizard can use a lot of CPU and memory as it does its analysis. Therefore it is recommended that you run the index tuning wizard in a non-production environment.
• When using a SQL Profiler trace, only the first 32,767 tunable queries will be considered.
• Since wizard makes index recommendations based on the workload file, it is important to have a workload file that simulates the normal set of queries that an application uses routinely, if you are not keeping the existing indexes.
• The Index Tuning wizard does not make index recommendations for commands that do cross-database joins.
• The index tuning wizard may not make recommendations for tables that have only a few rows of data.

Conclusion

The index tuning wizard is a valuable tool for a DBA. This tool can be used to recommend a set of indexes for a batch of queries, or for just a single query. The tool is rich in reports showing various space and usage statistics of existing indexes, as well as recommended indexes. The next time a programmer is having some performance issue with their application, you might consider running a workload sample from their application through the index tuning wizard. The wizard might just identify some indexing suggestions that would eliminate the performance bottleneck associated with the poorly performing application.

» See All Articles by Columnist Gregory A. Larsen