Index Tuning Wizard

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.
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles