Removing Query Hints with Plan Guides (Dear SQL DBA Episode 43)

If you need to add, remove, or replace hints from ad-hoc queries where you can’t change the code, plan guides can help. See a demo of removing a query hint from parameterized TSQL run from an application, and get tips on how to make your plan guides work in SQL Server.

The code from the demo is here. Links for more info are below the video. Have fun!

Option 1: Listen to the 30 minute audio podcast episode (or download it!)

You can listen on the run by subscribing to the podcast on iTunes , on Google Play, or plug this RSS feed into your favorite podcast app: http://dearsqldba.libsyn.com/rss

Option 2: Watch the YouTube video

This is the exact same content as in the audio podcast, but with visuals of the slides and demos.

Further reading

The video shows creating a plan guide with sp_create_plan_guide to remove a query hint, but you can use plan guides to do much more!

1. You can add or remove table hints (such as an index hint or a FORCESCAN hint), using slightly different syntax with sp_create_plan_guide. See examples in Books Online here.
2. You can force parameterization of specific queries using sp_create_plan_guide to create a ‘TEMPLATE’ type plan guide. See example syntax on this type of plan guide here.
3. You can “freeze” an execution plan so that it is always used for the query. For this one, you use the procedure sp_create_plan_guide_from_handle. See code on how to do this here.