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 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: