Index Tuning Wizard

July 29, 2005

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
                        FROM [Order Details] AS OrdD2
                        WHERE OrdD2.UnitPrice > $100)
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 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
     Northwind.dbo.Customers AS Cst
  ON (Cst.CustomerID = Ord.CustomerID)
SELECT DATEPART(yy, HireDate) AS Year,
       COUNT(*) AS NumberOfHires
FROM Northwind.dbo.Employees

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.

The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers