Contents
How to optimize parameter sniffing in SQL Server?
SQL Server provides a range of query hints and other options to tune the behaviour of parameter sniffing: The OPTIMIZE FOR (@parameter = value) query hint builds a reusable plan based on a specific value. OPTIMIZE FOR (@parameter UNKNOWN) uses average distribution statistics for a particular parameter.
Which is better parameter sniffing or parameter embedding?
Parameter Sniffing. Query parameterization promotes the reuse of cached execution plans, thereby avoiding unnecessary compilations, and reducing the number of ad-hoc queries in the plan cache. These are all good things, provided the query being parameterized really ought to use the same cached execution plan for different parameter values.
Which is a better option recompile or embedding?
Using WITH RECOMPILE effectively returns us to SQL Server 2000 behaviour, where the entire stored procedure is recompiled on every execution. A better alternative, on SQL Server 2005 and later, is to use the OPTION (RECOMPILE) query hint on just the statement that suffers from the parameter-sniffing problem.
Is there parameter sniffing in T-SQL scalar functions?
Parameter sniffing occurs with T-SQL functions as well, though the way execution plans are generated can make this more difficult to see. There are good reasons to avoid T-SQL scalar and multi-statement functions in general, so for educational purposes only, here is a T-SQL multi-statement table-valued function version of our test query:
Why does parameter sniffing apply to a function?
Because parameter sniffing applies to functions, these modules can suffer from the same unexpected changes in performance commonly associated with stored procedures. For example, the first time a function is referenced, a plan might be cached that does not use parallelism.
How to overcome parameter sniffing for the stored procedure?
It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure. You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed.
What is the difference between recompile and optimize for unknown?
OPTIMIZE FOR UNKNOWN doesn’t affect this feature of the engine. RECOMPILE suppresses this feature and tells the engine to discard the plan and not put it into the cache. Using (or not) actual parameter values during plan generation. Usually optimizer “sniffs” the parameter values and uses these values when generating the plan.