Overview Of Parameter Sniffing

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

So, let’s get started.

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

Here, we are creating a table Test_Param_Sniff,

Parameter Sniffing

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

 

Parameter Sniffing 

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.

Parameter Sniffing 

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

So, in order to check the performance, we need to clear the plan cache as well. 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 that will return us the data based on the StartDate and EndDate.

Parameter Sniffing 

Now, we will execute the procedure first with the narrow range of values and see its costing.

Parameter Sniffing 
 
Parameter Sniffing 

Please take a closer look 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.

Parameter Sniffing 

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

Parameter Sniffing 
 
Parameter Sniffing 

If you look at both the scenarios where we run the SP with narrow range first and SP with high range first, you will see the next time the stored procedure is called it used the same plan which is generated at the first place. So in the second example where we run the SP with Narrow Range second, it used the same plan as used 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 is also using the plan for the stored procedure with high range parameters. The above table is very small and the query is very simple. But this kind of processing may cause performance issues on a very large data scale and if the queries are complex. So sometimes it is avoidable to use such an approach, rather 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 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,
    1. Create Proc Usp_Test_Param_Sniff  
    2. @StartDate SmallDateTime, @EndDate SmallDateTime  
    3. WITH RECOMPILE  
    4. As  
    5. Begin  
    6. Select * from Test_Param_Sniff Where Sdate Between @StartDate and @EndDate  
    7. End 
  1. Use 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 procedure, rather we can recompile the batch which is using the parameters and change its execution plan depending upon the parameters. For instance,
    1. Create Proc Usp_Test_Param_Sniff  
    2. @StartDate SmallDateTime, @EndDate SmallDateTime  
    3. As  
    4. Begin  
    5. Select * from Test_Param_Sniff Where Sdate Between @StartDate and @EndDate  
    6. Option(Recompile)  
    7. End  
  1. Use Local Variables instead of the parameter variables. For instance,
    1. Create Proc Usp_Test_Param_Sniff  
    2. @StartDate SmallDateTime, @EndDate SmallDateTime  
    3. As  
    4. Begin  
    5. Declare @SD SmallDateTime = @StartDate;  
    6. Declare @ED SmallDateTime = @EndDate;  
    7. Select * from Test_Param_Sniff Where Sdate Between @SD and @ED  
    8. End  

So, these are the given ways in which we can prevent SQL Server frm using the same execution plan for the execution with different parameters as the data distribution is different for every query. The article may be confusing somewhat in terms of the notations used for the examples. But just keep in mind 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. 

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