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.

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.

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.

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

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:
 

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:
 

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

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.
 

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:
 

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:


And if I execute the query with @DateIssued = ‘20180212’ I will get the following execution times:


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!