Index Tuning Wizard

Deciding which indexingstrategy to apply is not an easy task. Different queries can be optimized in different ways using different indexes. To decide which is the best indexing strategy, it would be necessary to consider statistically which strategy produces the best global performance.

The Index Tuning Wizard does just that. It uses a trace from SQL Profiler to analyze, propose, and apply, if required, the best indexing strategy for the actual database workload.

With the integration of the Index Tuning Wizard in SQL Query Analyzer, it is possible to optimize a single query or batch in Query Analyzer, without creating a trace with SQL Profiler. This can be considered as a provisional solution, to speed up the process of one specific query. However, the best approach is still to use a trace that is representative of the actual database workload.

The process of using the Index Tuning Wizard is almost the same in both cases; that's why you will learn how to optimize a single query from Query Analyzer in this chapter. The query to optimize is represented in Listing 6.35.

Code Listing 6.35. You Can See How to Optimize the Following Query Using Index Tuning Wizard

					
USE Northwind
GO

SELECT OD.OrderID, O.OrderDate,
C.CompanyName, P.ProductName,
OD.UnitPrice, OD.Quantity, OD.Discount
FROM [Order Details] AS OD
JOIN [Orders] AS O
ON O.OrderID = OD.OrderID
JOIN [Products] AS P
ON P.ProductID = OD.ProductID
JOIN [Customers] AS C
ON C.CustomerID = O.CustomerID
WHERE Country = 'UK'

Write the query in Query Analyzer and select the complete query with the mouse.

Open the menu Query—Index Tuning Wizard, or press Ctrl+I. The Index Tuning Wizard will start and it will show the Welcome form. Click Next in this form and you will see the form shown in Figure 6.26.

In this form, you can decide whether you want to keep existing indexes; for the example, uncheck the check box so you don't consider any index as fixed.

You can select whether the wizard can consider the creation of indexed view. Leave the check box checked.

Select Thorough Tuning Mode to get better results. Click Next.

To Specify Workload, leave the SQL Query Analyzer Selection option set and click Next. If you followed the preceding instructions, the Index Tuning Wizard will be as shown in Figure 6.27.

Select the Orders, Products, Customers, and Order Details tables to tune, and then click Next.

The Index Tuning Wizard starts analyzing and, after a few minutes, shows index recommendations. You can review and select which recommendations are valid for you, according to your experience and the knowledge of the actual data. Note that the Index Tuning Wizard estimates the relative performance improvement when applying the new index strategy.

Click Next. Now either you can apply the changes directly or scheduled to a certain time, or you can script these changes for further analysis. Select to save the script and provide a filename. You should receive a script similar to that in Listing 6.36.

Click Finish to end the wizard.

Code Listing 6.36. These Are the Recommendations of the Index Tuning Wizard to Optimize the Query of Listing 6.35

					
/* Created by: Index Tuning Wizard     */
/* Date: 25/10/2000             */
/* Time: 23:36:33             */
/* Server Name: BYEXAMPLE             */
/* Database Name: Northwind             */
USE [Northwind]
go

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
go

DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

DROP INDEX [dbo].[Orders].[ShipPostalCode]
DROP INDEX [dbo].[Orders].[ShippedDate]
DROP INDEX [dbo].[Orders].[CustomersOrders]
DROP INDEX [dbo].[Orders].[OrderDate]
DROP INDEX [dbo].[Orders].[CustomerID]
DROP INDEX [dbo].[Orders].[ShippersOrders]
DROP INDEX [dbo].[Orders].[EmployeesOrders]
DROP INDEX [dbo].[Orders].[EmployeeID]
CREATE NONCLUSTERED INDEX [Orders7]
ON [dbo].[Orders] ([OrderID] ASC, [CustomerID] ASC, [OrderDate] ASC )

IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

BEGIN TRANSACTION
SET @bErrors = 0

DROP INDEX [dbo].[Order Details].[ProductID]
DROP INDEX [dbo].[Order Details].[orderID]
DROP INDEX [dbo].[Order Details].[price]
DROP INDEX [dbo].[Order Details].[OrdersOrder_Details]
DROP INDEX [dbo].[Order Details].[ProductsOrder_Details]
DROP INDEX [dbo].[Order Details].[ndxSale]

IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

BEGIN TRANSACTION
SET @bErrors = 0

DROP INDEX [dbo].[Customers].[Region]
DROP INDEX [dbo].[Customers].[CompanyName]
DROP INDEX [dbo].[Customers].[Contact]
DROP INDEX [dbo].[Customers].[ndx_Customers_City]
DROP INDEX [dbo].[Customers].[PostalCode]
DROP INDEX [dbo].[Customers].[City]

IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

BEGIN TRANSACTION
SET @bErrors = 0

DROP INDEX [dbo].[Products].[C_Products_Category_Price]
DROP INDEX [dbo].[Products].[CategoriesProducts]
DROP INDEX [dbo].[Products].[SuppliersProducts]
DROP INDEX [dbo].[Products].[CategoryID]
DROP INDEX [dbo].[Products].[ProductName]
DROP INDEX [dbo].[Products].[SupplierID]

IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

/* Statistics to support recommendations */

CREATE STATISTICS [hind_325576198_1A_2A_3A_4A_5A]
ON [dbo].[order details] ([OrderID], [ProductID], [UnitPice],
[Quantity], [Discount])

Now you can modify this script to meet other requirements and execute against the server to apply the changes.