Index Tuning Wizard

Friday Jul 29th 2005 by Gregory A. Larsen
Share:

Gregory Larsen explains how to use SQL Server 2000's index tuning wizard to tune the indexes on your database.

There are a number of wizards in SQL Server 2000. One of these wizards is known as the index tuning wizard. The index tuning wizard is used for exactly what it sounds like; it is used for tuning indexes. This article will introduce you to the index tuning wizard and will explain how you can use it to tune the indexes on your database.

What is the Index Tuning Wizard

The index tuning wizard is a utility that comes with SQL Server that makes recommendations on how indexes should be built on a database to optimize performance. These recommendations are derived based on T-SQL commands that the wizard analyzes. The wizard makes it easy to tune your indexes without any great understanding of SQL Server index structure and/or database internals. The index tuning wizard can also determine how a proposed change might affect performance. The wizard has the capability to make index changes immediately, schedule them for a later timeframe, or build a T-SQL script to create the indexes.

T-SQL Command formats accepted by the Index Tuning Wizard

As stated above, the index tuning wizard needs to analyze a set of SQL commands to make a recommendation. The wizard accepts these commands in a number of different formats. You can provide a file that contains a single T-SQL statement or set to T-SQL commands. You can save the output of a Profiler trace to a SQL Server table, or a trace file and then use the table or trace file as input to the wizard. The wizard can also take the T-SQL statements from a Query Analyzer pane and run them through the wizard. These different input formats allow you flexibility in how you gather the set of T-SQL commands you would like to analyze with the index tuning wizard.

Analyzing a Set of T-SQL Statements

Let me walk you through the process of analyzing a set of T-SQL statements. For this walk through I am going to use the following T-SQL statements that access data in the Northwind database:

SELECT OrdD1.OrderID AS OrderID,
       SUM(OrdD1.Quantity) AS "Units Sold",
       SUM(OrdD1.UnitPrice * OrdD1.Quantity) AS Revenue
FROM [Order Details] AS OrdD1
WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID
                        FROM [Order Details] AS OrdD2
                        WHERE OrdD2.UnitPrice > $100)
GROUP BY OrdD1.OrderID
HAVING SUM(OrdD1.Quantity) > 100

SELECT LastName AS EmployeeLastName,
       OrderID, OrderDate
FROM Northwind.dbo.Orders AS Ord
     JOIN Northwind.dbo.Employees as Emp
       ON (Ord.EmployeeID = Emp.EmployeeID)
WHERE OrderDate > '31 May, 1996'
SELECT Ord.OrderID, Ord.OrderDate,
       (SELECT MAX(OrdDet.UnitPrice)
        FROM Northwind.dbo.[Order Details] AS OrdDet
        WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice
FROM Northwind.dbo.Orders AS Ord
SELECT ProductName
FROM Northwind.dbo.Products
WHERE UnitPrice =
      (SELECT UnitPrice
       FROM Northwind.dbo.Products
       WHERE ProductName = 'Sir Rodney's Scones')
SELECT * FROM Northwind.dbo.Products WHERE CategoryID = 4
SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'
SELECT UnitsInStock, ProductID, ProductName
FROM Northwind.dbo.Products
WHERE UnitsInStock = 15 OR UnitsInStock = 25
ORDER BY UnitsInStock
SELECT CompanyName, OrderID, ShippedDate
FROM Northwind.dbo.Customers AS Cst
     JOIN Northwind.dbo.Orders AS Ord
       ON (Cst.CustomerID = Ord.CustomerID)
SELECT * FROM Northwind.dbo.Products
WHERE ProductID = 63

SELECT Cst.CustomerID, Cst.CompanyName, Cst.ContactName,
       Ord.ShippedDate, Ord.Freight 
FROM Northwind.dbo.Orders AS Ord
  JOIN
     Northwind.dbo.Customers AS Cst
  ON (Cst.CustomerID = Ord.CustomerID)
SELECT DATEPART(yy, HireDate) AS Year,
       COUNT(*) AS NumberOfHires
FROM Northwind.dbo.Employees
GROUP BY DATEPART(yy, HireDate)

The index tuning wizard can be started by opening Enterprise manager, expanding the server group, selecting the server that contains the database you want perform index tuning against, click on the "Tools" menu, select wizards, expand the "Management" item, then double click on the "Index Tuning Wizard." The index tuning wizard can also be started from Query Analyzer, by selecting the "Index Tuning Wizard" from the "Query" menu, or using the Ctrl-I shortcut. You also can use the "Tools" menu in Profiler to start the index tuning wizard. When the wizard starts, the welcome page will be displayed. Click on the "Next>" button and the following "Select Server and Database" page will be displayed.

On the screen above, you need to select the database that you are going to tune, and select how you would like the wizard to perform. If you are just tuning a couple of problem queries, you will want to check the "Keep all existing indexes," but if you have a large workload file that is a complete representation of the commands that will normally be processed against your database, then you may want to uncheck this option to allow the index tuning wizard to drop any existing indexes, and recreate a completely new set of indexes that will optimize the queries being analyzed. You will also need to determine if you want to "Add indexed views." You will need to determine the appropriate "Tuning Mode" for your analysis. Keep the following things in mind when selecting a mode:

  • The "Fast" mode does the least amount of analysis, does not recommend new clustered indexes, or indexed views, as well as it keeps all existing indexes.
  • The "Thorough" mode performs a complete analysis of the workload queries, although it can run considerable longer than the other modes.

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

Click for larger image

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.

Click for larger image

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved