On occasion I’ve been in situations where I’ve identified a query with a hint applied that was causing degraded performance.  In the case of vendor code this can be a problem since removal or changes to hints would require a code change.  This can also be a problem in shops where there are strict processes in place to promote code through the SDLC – this is not always a convenient scenario when fire fighting a production problem.

This post outlines a fairly simple way to remove a Query hint without requiring code changes.  This approach does involve plan guides, which many people shy away from due to some complexities; however, I will show you a shortcut that really simplifies creating plan guides.

Query Hint Vs Table Hint

I’ll need to start off by clarifying this post outlines removing Query Hints.  Table hints can’t be removed, but they can be modified.  I’ll get more into that later.  For now I’ll start with the removal of a query hint.  A query hint is placed at the end of the query with the OPTION key word.  In this first example I’ll show how to remove any query hints.

Plan Guides

As I mentioned previously a query hint can be removed via a plan guide.  This is pretty simple.  Set the @hints parameter to NULL when creating the plan guide and it will remove the query hint.  The difficult part of doing this is usually creating the plan guide.  I’ll share a shortcut to this which would help simplify the whole process.

Plan Guide Short Cut

A while back I shared a post where I explained how to create a plan guide by using the procedure sp_create_plan_guide_from_handle.  We can use this approach to generate a working plan guide.  We can then script the plan guide from SSMS, and subsequently modify the @hints parameter to apply the NULL hint.  That being said I will refer you to my earlier post.

Example

In this example we have a query with a FORCE ORDER hint that is degrading performance.  We want to remove the FORCE ORDER hint via a plan guide.

SELECT * FROM Person.Person p INNER JOIN
CustomerTest ct ON ct.PersonID = p.BusinessEntityID WHERE AccountNumber = @AccountNumber
OPTION(FORCE ORDER)

As a first step you will need to use sp_create_plan_guide_from_handle by following my earlier post.

Following those instructions we end up with the following command to create the plan guide that we will use as a template.

exec sp_create_plan_guide_from_handle
@name = N'Place holder for template',
@plan_handle = 0x060007007300341F006F2F24D901000001000000000000000000000000000000000000000000000000000000,
@statement_start_offset = 56

Once the plan guide is created, we can go about scripting it out via SSMS.  We’ll need to open up SSMS and locate the plan guide. It will be located under the Database/Programmability/Plan Guides.

Right click on this to script the plan guide out as a create statement.

Next we’ll delete the place holder plan guide. We must delete this prior to creating the modified plan guide or else the following error will occur when running the command: “Cannot create plan guide ‘******’ because the statement specified by @stmt and @module_or_batch, or by @plan_handle and @statement_start_offset, matches the existing plan guide ‘Place holder for template’ in the database.

Next we’ll modify the @name parameter and @hints parameter for the plan guide. The @hints parameter will be an NVARCHAR value containing XML.  Be careful not to change the format (i.e. don’t even add or remove lined feeds or whitespace) of the @stmt, @module_or_batch, and @params parameters.

The modified plan guide is below.

After we execute the modified command we should have a new plan guide that removes the FORCE ORDER hint.

Table hints

As I mentioned earlier we can only remove a query hint.  Table hints can be modified, but cannot be removed.  If you feel you an improper index is being taken you can use the TABLE HINT syntax to change what index is being accessed.  You can also apply hints such as FORCE SCAN and FORCE SEEK.  The only catch with this is the table alias must be unique within the query.  I won’t go too far into this, because this can be a whole other post in itself.

Conclusion

Although I did not explicitly call it out, hints can also be added via the plan guide shortcut I outlined above.  Query store is a nice feature for dealing with plan regressions; however, in the scenario we described above we can’t accomplish the task of removing hints.  Happy tuning everyone.