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
» 
