Parameter Sniffing In SQL Server

SQL Server uses a process called parameter sniffing when it executes a stored procedure that has parameters. When the procedure is compiled or recompiled, the value passed in the parameter is evaluated and used to create an execution plan. The same plan will be stored in plan cache. Technically, the process of Query compilation or recompilation is costly so we want SQL to use the plan which is already in the plan cache. This is normal and expected behavior of SQL Server.

Now, when the first time this procedure is executed with parameter, the plan which was generated by SQL Server will be stored in plan cache. When the procedure is executed with different parameters, the same plan will be used.

Let’s assume that the data in table is not evenly distributed. One parameter value retrieves 10 rows, another parameter retrieves 10000. In that case, we want SQL to use the optimum plan for data retrieval but as the procedure is parameterized, it will use the same plan regardless of if it is optimum for data retrieval or not.

 

The below example will demonstrate parameter sniffing.

I am going to run two queries to show you the behavior. I will be using WideWorldImporters database on SQL Server 2016.

select * from [Sales].[CustomerTransactions] where CustomerID=401
select * from [Sales].[CustomerTransactions] where CustomerID=976

Below is the execution plan.

 

In the above example, the first SQL query  thinks that for customerID 401, Index Scan will be a more appropriate option so instead of using Index Seek and key lookup, it used Index Scan.

Now, I have created a stored procedure for the same query and executed it.

1. exec GetCustomerTransaction 401

Execution plan for customerID 401.



For customerID 401, 23233 rows have been returned. Considering the amount of data being retrived, SQL uses Index Scan.

Let's use another parameter for the same procedure.

1. exec GetCustomerTransaction 976

Execution plan for customer ID 976.



For customerID 976, only 250 rows are returned but still SQL is doing Index Scan. Also, see the difference between estimated number of rows and actual number of rows.

This is parameter sniffing in action. One value is stored in the execution plan and that is used to create the plan regardless of what value is passed in.

Now, the very first question is: Is parameter sniffing bad?

The answer is:

It depends on the query and the amount of data being returned. Each execution of this query may return the results in an acceptable amount of time. It is only when query performance reaches an unacceptable threshold that you may need to do something to prevent it from happening.