Overview Of Parameter Sniffing

Introduction

Hello learners. I hope you all are doing great. I am writing this article to give you an understanding of parameter sniffing. What is it all about, and how is it related to performance?

Parameter sniffing in SQL Server

Parameter sniffing, as the name suggests, relates to sniffing the parameters. When you execute any ad-hoc query or stored procedure, the SQL server generates a plan for every query or stored procedure. When you create any stored procedure with parameters, the SQL server sniffs the parameter range or type and generates a cost-effective execution plan. And this can be the difference in performance sometimes.

Here, we are creating a table Test_Param_Sniff,

Now, we will create a Clustered Index on the column SDate,

The next step is to fill the table with some random data. Please do not go into the SDate values as they will exceed the current year, 2018, and will be till 2057, according to the logic. The above query fills up the data from 1991 to 2057. That is why we call it random data. Moreover, this is just for understanding.

So, we all are ready for the demonstration now. Before proceeding, I would like to inform you that we will use a DBCC command to clear or clean up our plan cache. I hope you all are aware of the plan cache. If not, then it is a part of the buffer pool, and there is a designated part of the buffer pool assigned to the plan cache, which stores all the execution plans that the SQL server creates when you execute any ad-hoc query or stored procedure. The SQL server uses the plan saved in the plan cache to execute the same query or stored procedure again.

So, to check the performance, we also need to clear the plan cache. So, we will use the following command to achieve that.

DBCC FREEPROCCACHE

This will clear the plan cache and remove all the existing plans available with the SQL server, and the next time you execute the query, a new plan will be generated.

Now, we will create a procedure to return the data based on the StartDate and EndDate.

We will execute the procedure first with a narrow range of values and see its cost.

Please look closely at the estimated cost tags and check the values. Now, we will run the procedure without clearing the cache and with a higher range and check its cost.

This time we will clear the plan cache and execute the procedure first with the high range of values, then with the narrow range, check the cost, and compare with the Vice-Versa operation we did earlier. We will first run DBCC FREEPROCCACHE,

If you look at both scenarios where we run the SP with a narrow range first and SP with a high range first, you will see the next time the stored procedure is called, it uses the same plan which is generated in the first place. So in the second example where we run the SP with a Narrow Range second, it used the same plan for SP with High Range First -- but see the cost difference in the executions where we used narrow range parameters first and second.

The cost is more in the second case as the stored procedure with the narrow range parameters also uses the plan for the stored procedure with high range parameters. The above table is tiny, and the query is very simple. But this kind of processing may cause performance issues on a huge data scale if the queries are complex. So sometimes it is avoidable to use such an approach, rather than generate an execution plan every time it executes the stored procedure with different parameters all the time.

Below are the ways to avoid parameter sniffing.

1. Create the stored procedure WITH the RECOMPILE option. This method will compile the Stored Procedure each time it is executed, and the execution plan will be generated depending on the current state of parameters. For instance,

   Create Proc Usp_Test_Param_Sniff  
   @StartDate SmallDateTime, @EndDate SmallDateTime  
   WITH RECOMPILE  
   As  
   Begin  
   Select * from Test_Param_Sniff Where Sdate Between @StartDate and @EndDate  
   End 

2. Use the hint Option (Recompile) for the SQL query. Sometimes the Stored Procedures are complex, and it is not a good idea to recompile such complex stored procedures; instead, we can recompile the batch using the parameters and change its execution plan depending on the parameters. For instance,

   Create Proc Usp_Test_Param_Sniff  
   @StartDate SmallDateTime, @EndDate SmallDateTime  
   As  
   Begin  
   Select * from Test_Param_Sniff Where Sdate Between @StartDate and @EndDate  
   Option(Recompile)  
   End  

3. Use Local Variables instead of parameter variables. For instance,

   Create Proc Usp_Test_Param_Sniff  
   @StartDate SmallDateTime, @EndDate SmallDateTime  
   As  
   Begin  
   Declare @SD SmallDateTime = @StartDate;  
   Declare @ED SmallDateTime = @EndDate;  
   Select * from Test_Param_Sniff Where Sdate Between @SD and @ED  
   End  

So, these are the given ways to prevent SQL Server from using the same execution plan for the execution with different parameters, as the data distribution is different for every query. The article may be somewhat confusing regarding the notations used for the examples.

But remember that in the first example, we ran the Stored Procedure with the Narrow Range Parameters first and then with High Range Parameters. And in the second example, we ran the Stored Procedure with the High Range Parameters first and then with Narrow Range Parameters. 

Summary

I hope this article helps you understand the concept of Parameter Sniffing in SQL Servers. Happy Learning! Any feedback will be considered for betterment.