5/30/2018 5:06:54 PM Dean Savović, Head of Data Management Department
SQL Parameter Sniffing
 
Parameter sniffing is a great feature of SQL Server that helps execute queries with less CPU and memory consumption, but sometimes can go bad on us. Meaning our queries can take longer execution times, use more IO and CPU. Why does this happen and how to deal with it I will describe in this blog post.
 

What is  SQL parametar sniffing?

SQL Server, if recompile is not specified, when executing stored procedure or ad-hoc query stores the execution plan in the plan cache. This execution plan is compiled with run-time value of parameters of stored procedure or ad-hoc query. So, what SQL Server does is, it sniffs the parameter values and stores them with query plan in plan cache. This is a great feature, because when the same stored procedure or ad-hoc query is executed again with same or different parameters, SQL Server does not have to compile the execution plan again, but it reuses the plan from the cache saving the CPU and memory that would be used to compile the execution plan again.
 

When parametar sniffing goes bad on you?

From now on we will focus on stored procedure behavior, although everything stated from now on can be applied to parameterized ad-hoc queries also. Sometimes you experience that some stored procedure executes fine for a long or short period of time and then all of a sudden stored procedure executions become slower. Another situation is that for some input parameters stored procedure executes fine and for other execution time is horrible. For these cases the cause may be bad parameter sniffing problem, meaning that execution plan for different parameters for stored procedure should result in the different execution plan, but because of parameter sniffing feature of SQL Server this does not happen.
 

How to dealwith bad parametar sniffing?

There are many ways you can deal with bad parameter sniffing. Here are just a few of them:
  1. Hint optimize for unknown
  2. Trace flag 4136
  3. ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF
  4. Hint Optimize for Value
  5. Hint Option Recompile
  6. Dynamic SQL
  7. Dynamic SQL with Parameters
  8. Dynamic SQL with Option Recompile
I would like to state that although Option Recompile will give you the best results don’t go using it everywhere in the code. Downside of option recompile is that execution plan will not be stored in the plan cache and SQL Server will use additional CPU and memory to create the execution plan on every execution which can be devastating for SQL Server resources on heavy transactional systems that execute the same procedure very often.
 

Bad parametar sniffing case

In this part I will describe a bad parameter sniffing example. For example I have a table dbo.t Questionnaire with the following structure:
 
Slika-1.png

Table has 3 million rows generated with RedGate SQL Data Generator. Table has two non-clustered indexes on DateIssued and Name columns with following structure:
 
slika-2.png

There is a multi-purpose stored procedure that looks like this:

slika-3.png

This is a perfectly valid stored procedure that returns top 10 rows for given @Name and @DateIssued and returns all rows if @Name or @DateIssued is not supplied (top 10). I will call this stored procedure with @Name = ‘Brighton’ parameter supplied (there is nothing in the procedure cache at this point in time), so the execution plan will be created with @Name = ‘Brighton’ and @DateIssued = NULL. The procedure call looks like this:
 
mala-slika-1.png

Execution time for the stored procedure call above is 553 ms:
mala-slika-4.png

Second procedure call will be suppling @DateIssued = ‘20180212’ and @Name = NULL:
mala-slika-2.png
 
Execution time for this procedure call is 12907 ms:
mala-slika-3.png

I am suspecting that the second procedure call is suffering from bad parameter sniffing problem so let’s try to prove that.
 
slika-5.png

If we look at the execution plan of second execution we will see that index on Name column is being used. Also let’s look at the compiled parameter values against run-time parameter values.
 
slika-6.png

In the above picture you can see that plan is retrieved from plan cache and that compiled value for @DateIssued is NULL and run-time value is ‘2018-02-12’. We have confirmed that this is a bad parameter sniffing problem. Now, how to deal with it? I have already enumerated various methods how to deal with bad parameter sniffing problem and now I will use the dynamic SQL with parameters method as this is my favorite. What I am doing here is building the where clause of the query only if the @Name or @DateIssued parameters are not null. This way for the two above procedure calls we will get two execution plans because this will not be the same query and this way we will resolve the bad parameter sniffing problem. The new stored procedure looks like this:
 
slika-7.png

You see that this is the same query as before but with variable where clause. I am also using dummy “1 = 1” in the where clause to make it easier to append AND where parts. If I execute the stored procedure with @Name = ‘Brighton’ I will get the following execution times:
mala-slika-5.png

And if I execute the query with @DateIssued = ‘20180212’ I will get the following execution times:
mala-skika-7.png

If I look at the execution plans I will see that first execution is using index on Name column and the second one index on DateIssued column. Just as I would expect.
 

Parameter sniffing is a feature of SQL Server, not a bug or something that is wrong. Most of the time parameter sniffing is helping SQL Server to use less CPU and memory and when it goes bad on us you know how to deal with it. One of the proposed methods of dealing with bad parameter sniffing was described in detail in this blog post. Enjoy!
 
 

Tags: Optimization, Parameter, Performance, Server, Sniffing, SQL, Tuning

Share