SQL Server 2005 Plan Guides and Index Hints

I was posed a question the other day – “Can query plan guides help when an index is explicitly chosen via a hint in the query?”.  The short answer is yes, but let’s have a look at the process of creating a plan guide.

First, we need to create a situation where there’s two indexes that could be used.  I’ve chosen to use the Production.Product table in the AdventureWorks database, and created an index  on ProductID and Name:

CREATE NONCLUSTERED INDEX ncix_ProductID_Name ON Production.Product (ProductID, Name)

 

If we then execute the following query, the new index will be used:

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID BETWEEN 300 AND 320

 

However, if we use an index hint, we can force the query to use the clustered index:

SELECT ProductID, Name
FROM Production.Product WITH (INDEX = PK_Product_ProductID)
WHERE ProductID BETWEEN 300 AND 320

 

Now, let’s create a query plan that will always use the non-clustered index, regardless of whether the index hint says to use the clustered index or not.  First, we need to create a Profiler trace to capture the XML Show Plan event, and we run the query, forcing the non-clustered index.  This results in an XML plan of, which we then create a Plan Guide with:

EXEC sp_create_plan_guide
    @name = N'TestPlanGuide1',
    @stmt = N'SELECT ProductID, Name
FROM Production.Product WITH (INDEX = PK_Product_ProductID)
WHERE ProductID BETWEEN 300 AND 320',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION(USE PLAN N''<ShowPlanXML ...</ShowPlanXML>'')'

 

This doesn’t quite fit on the screen nicely (and temporarily removed as it doesn’t play nice with IE6), but you get the idea.  Now, we can run the query above, providing a WITH (INDEX = PK_Product_ProductID) hint, and we can see that the hint has been ignored in favour of the plan guide.

SELECT ProductID, Name
FROM Production.Product WITH (INDEX = PK_Product_ProductID)
WHERE ProductID BETWEEN 300 AND 320

 

Finally, drop the plan guide using this command:

sp_control_plan_guide N'DROP', N'TestPlanGuide1'

 

So, even if you have queries that you can’t modify that have index hints, you can still add in a plan guide to force the plan you require.