다음을 통해 공유

Execution plan for stored procedures chosen by SQL Server Database Engine

  • 아티클

Execution plan for stored procedures chosen by SQL Server Database Engine

I think everybody wondered why we have different execution plans chosen by the SQL Server Database Engine for the same stored procedure.

You can find the answer in the following blog. 

Because the SQL Server Engine does a recompilation, taking into consideration the current parameter  when the recompilation is done, the engine choses the optimal execution plan for that parameter. This means that it can choose different execution plan if the current parameter has another optimal execution plan than the last one. It is absolutely normal the chosen execution plan be different than the last one (with a different parameter for the stored procedure ).

This execution plan chosen must be optimal for the current value of the parameter of the stored procedure, it is not necessarily  optimal for the rest of the parameters.  

To better explain the things I said above I would like to introduce the notion of ‘parameter sniffing’, better explained in the white paper: Plan Caching in SQL Server 2008

When a stored procedure is compiled for the first time (or in fact any parameterized batch), the values of the parameters supplied with the execution call are used to optimize the statements within that stored procedure. This process is known as "parameter sniffing”.

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter value during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans.

The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation. Parameter values are sniffed during compilation or recompilation for the following types of batches:

- Stored procedures

- Queries submitted via sp_executesql

- Prepared queries

- OPTION(RECOMPILE) query hint

For such a query (could be SELECT, INSERT, UPDATE, DELETE or MERGE), both the parameter values and the current values of local variables are sniffed. (Without OPTION(RECOMPILE), only parameter values are sniffed). The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the OPTION(RECOMPILE) hint. In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query : Execution Plan Caching and Reuse

The conditions that invalidate a plan include the following:

  • Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • Changes to any indexes used by the execution plan.
  • Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • Dropping an index used by the execution plan.
  • An explicit call to sp_recompile.
  • Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • Executing a stored procedure using the WITH RECOMPILE option.

Most recompilations are required either for statement correctness or to obtain potentially faster query execution plans.

If you want to use only one execution plan for all the parameters of the procedure you can apply one of the following solutions:

- You can choose a certain execution plan and you can add in the procedure Use Plan Query Hint, better explained in the article : Specifying Query Plans with Plan Forcing

- Another option is for you to use OPTIMIZE FOR  or FORCE RECOMPILE, better explained in the  articles Understanding Plan Guides  and Query Hints (Transact-SQL)   “Queries that can benefit from plan guides are generally parameter-based, and may be performing poorly because they use cached query plans whose parameter values do not represent a worst-case or most representative scenario. The OPTIMIZE FOR and RECOMPILE query hints can be used to address this problem. OPTIMIZE FOR instructs SQL Server to use a particular value for a parameter when the query is optimized. RECOMPILE instructs the server to discard a query plan after execution, forcing the query optimizer to recompile a new query plan the next time that the same query is executed.”

 

I hope that the information provided in this blog answered some of your questions.