Statistics are database objects that contains the information about the value distribution in one or more columns of a table and views. CompanyId = ( OPTIMIZE FOR UNKNOWN )ĭisable auto-update statistics during the batch OPTIMIZE FOR in a query hint allows us to specify a constant that is used by the optimizer while optimizing the query. This is a better way in which only a specific query is recompiled but in the above method entire the Stored Procedure is recompiled on every call. SQL Server offers the new query hint RECOMPILE that forces query recompilation. This solution enforces recompilation of the Stored Procedure on each run, so every time the optimizer creates a new execution plan for the current parameters.ĮXEC TestStoreProcedureNew 1002 WITH RECOMPILE CompanyId = Stored Procedure using "WITH RECOMPILE" option Use of a dummy variable that is not used as a parameter also ensures the stability of the execution plan. Use dummy variables that are not directly used as parameters In this article I am using the same example from my previous article: Using following, we may avoid the issue with parameter sniffing.
If the value of parameters is not known at the time of compilation then there is nothing to be sniffed. This looks beneficial when good statistcs are maintained and typical parameters are used.ĭisabling the parameter sniffing is helpful when there are several parameters in a Stored Procedure and there is also no favorite combination of parameters.
The optimizer will go and search the execution plan that is compiled earlier from the cache. The advantage of this behavior of the optimizer, is that the optimizer needs not re-create the execution plan to execute the same query multiple times. The cached execution plan may not be well-optimized for the new parameter, hence the second call of the query may be very slow. Parameter sniffing is enabled by default in SQL Server. The query optimizer will use the same execution plan for the next call of the Stored Procedure. If an execution plan is not present in the cache then the query optimizer creates a well-optimized execution plan based on input parameter values. If an execution plan already exists for a Stored Procedure or parameterized query in the cache then the query optimizer will blindly reuse it. Whenever a Stored Procedure or parameterized query is executed, the query optimizer tries to reuse the execution plan.
0 kommentar(er)
