SQL Server does the best it can to generate an efficient execution plan when a query is executed. This process can be expensive. To avoid the expense, SQL Server attempts to cache a plan once it is generated. This eliminates the need for subsequent calls to the same query to generate the plan. In an ideal world all executions of the same query should perform well with one plan. In reality there are other factors that can cause an efficient plan to generate for one execution but not for other executions. Causes of this (stale statistics, parameter sniffing) won’t be covered in this post, but options to get around this will.
In this post I’ll cover some zero code change options and some of their pros-cons and limitations.
Freeing A Bad Plan From Cache
In some cases, freeing a bad plan from the cache may solve your problems. In the case of parameter sniffing, this option has a higher chance of solving problems.
You can free a bad plan from the cache with either DBCC FREEPROCCACHE or by recompiling a stored procedure.
DBCC FREEPROCCACHE
If you plan to use DBCC FREEPROCCACHE I highly recommend passing a plan handle as input. This will prevent you from freeing every plan from the cache (good or bad). If you are reading this because you are a SQLGrease user, you can generate the DBCC FREEPROCCACHE command by clicking the “Copy flush command” button.
sp_recompile
If the query with the bad plan is in a stored procedure, you can call sp_recompile on the stored procedure. This will cause a new plan to generate for all statements in the stored procedure.
Forcing a Good Plan
Another option for dealing with bad plans is to force a known good plan. This scenario requires that you have the good plan. There are a couple options for this.
Query Store
If you have query store enabled (available on SQL Server 2016 and later), you can force a good plan to be used whenever a particular query is executed. You must have query store enabled to do this. Query store is currently not enabled by default except for Azure SQL Database. Query store must be enabled while the good plan was in use in order to force the good plan.
Plan Guides
Plan guides are generally complex to create and as a result some people shy away from them. With plan guides you can specify an execution plan as XML and force the query to use that plan whenever it is encountered. There are several options for how to create plan guides and I’ll list these out here. It should be noted, you will need to drop a plan guide if you applied it to a stored procedure and perform an update to the stored procedure at some later point in time.
sp_create_plan_guide
This is the most difficult option and usually takes a fair amount of trial and error. You’ll need to spend some time digging through the Microsoft documentation to get one working.
sp_create_plan_guide_from_handle
This is one level simpler (and I would say quite a bit simpler) than the first option. This is an out of the box feature with SQL Server which lets you force a good plan; however, the good plan has to be in the cache at the time you run this command. I have a blog post that describes this here.
usp_create_plan_guide_with_hint
A while back I put together a blog post that allowed you to craft a plan guide even if the good plan is currently not in the plan cache. This simplifies the process a bit more. Under the covers it relies on sp_create_plan_guide_from_handle. You can find that blog post here.
SQLGrease Plan Guide Generator
If you are a SQLGrease user, you can click the “Generate plan guide” button and it will generate a force plan for a previously collected execution plan. Under the covers it uses a lot of the logic similar to usp_create_plan_guide_with_hint in order to generate the plan guide. This does not require query store.
Conclusion
In most cases the optimizer does a pretty good job of generating efficient execution plans. In the rare occasions where the optimizer is unable to consistently generate good plans these techniques can help you get production issues solved with no code changes required.