Lesson Learned #310: Parameter Sniffing

[Guest Jose_Manuel_Jurado]

When we are using parameterized queries with object such as stored proecdure and functions, the engine can take the value passed to a parameter and use it to compare with statistics (index or colum). This is known as parameter sniffing

 

 

 

Basically, running a stored procedure the first compilation the optimizer "sniff" the parameter value and compile the query with the distribution of the statistics of this value.

 

 

 

In many cases, parameter sniffing reduces time in compilation and perform much better the execution, but, sometimes a high variance in terms of data distribution will impact in the performance.

 

 

 

For example, if the first execution of a query use a parameter value where will return a small subset of data but in the next execution with a different value the subset of the data is large, we may not have the best execution plan. Because, the query was compiled with a ten of hundres of rows and the new parameter value may has millions.

 

 

 

In the following video you could find an example of this parameter sniffing and how to resolve it. Of course, we have a lot of documentation to fix it SQL Server: How to determine parameter sniffing problem and how to handle it - Microsoft Q&A and OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature - Microsoft Community Hub . In 2022 or Compatibility level for 2022 we have a new option for this - Parameter Sensitive Plan optimization - SQL Server | Microsoft Learn

 

 

 

Video

 

 

 

 

 

 

 

 

Enjoy!

 

Continue reading...