Parameter sniffing refers to a process whereby SQL Server’s execution environment “”sniffs”” the current parameter values 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. In SQL Server, parameter values are sniffed during compilation or recompilation for the following types of batches:
• Stored procedures
• Queries submitted via sp_executesql
• Prepared queries
In SQL Server 2005, the behavior is extended for queries submitted using the OPTION (RECOMPILE) query hint. For such a query (could be SELECT, INSERT, UPDATE, or DELETE), both the parameter values and the current values of local variables 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. In the first exercise, we will look at the concepts of parameter sniffing. In the second and third exercise, we will look at different solutions available to deal with parameter sniffing.
Exercise 1: Parameter Sniffing Overview
Exercise 2: Parameter Sniffing Solutions Part 1
Exercise 3: Parameter Sniffing Solutions Part 2
Reviews
There are no reviews yet.