Hello Friends,

Here is another popular lab from Hands-On-Lab library: SQL Server Parameter Sniffing.

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

SQLMaestros Hands-On-Labs enables the practical way of learning. Each lab document consists of multiple exercises and each exercise consists of multiple tasks. Apart from step-by-step instructions, the HOL document contains images, explanations, observations, notes, tips & tricks to ensure a great learning experience. With each lab, you can practice a specific concept step-by-step. With our HOL documents, you will get hands-on with hundreds of topics in SQL Server & Microsoft Data Platform. Learn More.

Learn More

 

 

Happy Learning – The Practical Way.