Use Plan Guides to Circumvent Parameter Sniffing

<div>I ran into a situation today involving parameter sniffing which brought a box to its knees. Until recently I've gotten around parameter sniffing by copying parameter values to local variables but I've since learned about plan guides. Plan guides allow hints to be applied to specific parts of a stored procedure or query (if run using <strong>sp_executesql</strong>) without having to modify the query itself. Plan guides are available in SQL Server 2005 and 2008.<br/><br/>When you're in firefighting mode a plan guide can be both a safety net and a timesaver because you don't have to risk touching code inside procedures and you can easily disable\drop a plan guide if it doesn't fix the problem. Sometimes you might not have a choice, either, because the query causing the problem might be buried inside of a compiled binary.<br/><br/>As helpful as they are, I look at using plan guides as a stop gap and not a permanent fix. It would be easy enough to apply a plan guide that fixes the problem and move on to the next thing...but in a few months when a developer make a code change or the statistics on a table have changed the plan guide might not make sense anymore and you can find yourself back in firefight mode. My suggestion is to use the plan guide to get around the immediate problem, then implement a long term fix with an alternate option like using <strong>WITH RECOMPILE</strong> at execution time or put an <strong>OPTION (RECOMPILE)</strong> on the problematic statement(s).<br/><br/>For more suggestions on how to deal with parameter sniffing see Plamen Ratchev's Weblog:<br/><a href="http://pratchev.blogspot.com/2007/08/parameter-sniffing.html" target="_new">http://pratchev.blogspot.com/2007/08/parameter-sniffing.html</a><br/><br/>See BOL topic "Understanding Plan Guides" for specifics about plan guides:<br/><a href="http://msdn.microsoft.com/en-us/library/ms190417.aspx" target="_new">http://msdn.microsoft.com/en-us/library/ms190417.aspx</a></div>