Recompile a stored procedure6/15/2023 ![]() With plan guides you can specify an execution plan as XML and force the query to use that plan whenever it is encountered. Plan guides are generally complex to create and as a result some people shy away from them. Query store must be enabled while the good plan was in use in order to force the good plan. Query store is currently not enabled by default except for Azure SQL Database. You must have query store enabled to do this. 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. This scenario requires that you have the good plan. Forcing a Good PlanĪnother option for dealing with bad plans is to force a known good plan. This will cause a new plan to generate for all statements in the stored procedure. ![]() If the query with the bad plan is in a stored procedure, you can call sp_recompile on the stored procedure. 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. This will prevent you from freeing every plan from the cache (good or bad). If you plan to use DBCC FREEPROCCACHE I highly recommend passing a plan handle as input. You can free a bad plan from the cache with either DBCC FREEPROCCACHE or by recompiling a stored procedure. In the case of parameter sniffing, this option has a higher chance of solving problems. In some cases, freeing a bad plan from the cache may solve your problems. In this post I’ll cover some zero code change options and some of their pros-cons and limitations. Causes of this (stale statistics, parameter sniffing) won’t be covered in this post, but options to get around this will. In reality there are other factors that can cause an efficient plan to generate for one execution but not for other executions. In an ideal world all executions of the same query should perform well with one plan. ![]() This eliminates the need for subsequent calls to the same query to generate the plan. To avoid the expense, SQL Server attempts to cache a plan once it is generated. SQL Server does the best it can to generate an efficient execution plan when a query is executed.
0 Comments
Leave a Reply. |