Override Bad Table Hints With a Plan Guide

Unfortunately, we’ve all seen some third party applications pass some pretty awful SQL into databases. The worst of these always seem to be queries that cannot be edited or modified from within the application. Table/index and MAXDOP hints are a couple of the things I don’t like to see. These hints might be perfectly fine for a while, but as data grows and tables get larger, certain hints can prevent the query optimizer from working properly and providing the optimal execution plan.

I will be using the AdventureWorks2012 database for this example, and as part of the setup, I’ve already created the index below on the [Sales].[SalesOrderDetail] table. (It will be required for the FORCESEEK hint in my examples to work):

USE [AdventureWorks2012]
GO

CREATE NONCLUSTERED INDEX [idx_ProductID] 
ON [Sales].[SalesOrderDetail]
([ProductID] ASC)
GO

Let’s take this simple query with and without an added FORCESEEK hint as an example.

--SalesOrderDetail - 372,956 reads
SELECT * FROM Sales.SalesOrderDetail s WITH (FORCESEEK)
INNER JOIN Production.Product p ON s.ProductID = p.ProductID;

FORCESEEK_plan

--SalesOrderDetail - 1,246 reads
SELECT * FROM Sales.SalesOrderDetail s 
INNER JOIN Production.Product p ON s.ProductID = p.ProductID;

PlanGuide_plan

Using the WITH(FORCESEEK) hint, the query is doing 372,956 reads on the SalesOrderDetail table (with a pretty nasty Key Lookup), but without the hint, the query only does 1,246 reads (in this case, a scan is a good thing).

Let’s assume for this blog post that the application is passing in the query with the FORCESEEK hint, and that we cannot modify the application to remove the hint, even though we know, for a fact, that the query will perform much better without the hint. How are we supposed to remove the FORCESEEK hint and cut down on reads?

First, we need to take a look at adding another TABLE HINT to the query to override the existing FORCESEEK hint. There is a pretty nifty trick to do this. We can simply append ‘OPTION(TABLE HINT(s))’ to the end of the query and it’ll wipe out any prior TABLE HINT on the SalesOrderDetail table (aliased as ‘s’). Since we aren’t actually specifying any specific TABLE HINT, it basically resets all existing table hints on SalesOrderDetail. It brings us back down to 1,246 reads and gives us the query plan with the Clustered Index scan.

--SalesOrderDetail - 1,246 reads
SELECT * FROM Sales.SalesOrderDetail s WITH (FORCESEEK)
INNER JOIN Production.Product p ON s.ProductID = p.ProductID
OPTION(TABLE HINT (s)); --TABLE HINT reset

Now that we’ve figured out how to add to the existing query to remove the FORCESEEK hint, we need to figure out how to automatically have this statement appended to this query every time it is executed. Two words: Plan Guide.

Here is the basic syntax for getting this plan guide added to the database. You must give it a name, specify which SQL statement the hint will be added to, the type of plan guide, and the hint that will be added to the SQL Statement. (NOTE: the SQL statement must be passed into @stmt EXACTLY as the application will pass it in to the database. This even includes trailing spaces – which have caused me headaches many times.)

USE [AdventureWorks2012]
GO

EXEC sp_create_plan_guide 
@name = N'RemoveFORCESEEK', 
@stmt = N'SELECT * FROM Sales.SalesOrderDetail s WITH (FORCESEEK)
INNER JOIN Production.Product p ON s.ProductID = p.ProductID;', 
@type = N'SQL', 
@hints = N'OPTION(TABLE HINT (s))'

GO

Now that the plan guide is created, we can test running the original query with the FORCESEEK hint.

query_planguide

We can see from the execution plan properties that the query is now using the Plan Guide and doing the Clustered Index scan on SalesOrderDetail for 1,246 reads. We’ve successfully circumvented the bad FORCESEEK hint and saved a ton of reads in the process.

Checkout this MSDN link for further reading on Query Hints:
https://msdn.microsoft.com/en-us/library/ms181714.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s