SQL Server trace flag 8602

Sometimes you may stumble upon a slow running query in SQL server and when you look at the code you find an index hint. In normal cases the query runs fast and you need to investigate why the the query suddenly runs slower. One option you could try is to remove the query hint and run the query to see if you get a different execution plan and faster query. An easier way without changing the query is to use the trace flag 8602. This trace flag disables all index hints in your session or at a global level.

You can try the code below to see the difference.

USE [AdventureWorks2008R2]
GO

CREATE PROCEDURE Sales.spGetCustomerWithIndexHint @customerID INT = NULL
AS
SELECT * 
FROM Sales.Customer c WITH(INDEX(AK_Customer_AccountNumber)) -- force a specific (incorrect) index for demonstration 
WHERE 
	c.[CustomerID] = @customerID;
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

--execute the procedure
EXEC Sales.spGetCustomerWithIndexHint 1

--activate the traceflag and try again
DBCC TRACEON(8602,-1)
GO
EXEC Sales.spGetCustomerWithIndexHint 1
DBCC TRACEOFF(8602)
GO
--compare the execution plans 
--and the statistics time and IO in the message window
--as you can see when using the index hint,
--you get and index scan and a key lookup
--Without the index hint, you get an clustered index seek

In this case, you notice that the index you are trying to force is causing SQL server to do key lookups and is causing the performance issues. To solve this issue you remove the index hint or change the query to return only the columns you need and change the index to include all the columns you have specified in the query.

Index hints can sometimes help, but as your database grow and data is changing, you might end up in a situation where SQL server should have chosen a faster execution plan.

If you are experiencing performance issues, feel free to contact us, and our highly skilled SQL server consultants will help you.

/Håkan Winther
@h_winther