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 into the parameter is evaluated and used to create an execution plan that will be stored in the plan cache. The process of query compilation, or recompilation, is costly so we want SQL Server to use the plan that is already in the plan cache. This is the normal and expected behavior of SQL Server.

The first time a procedure will be executed with a parameter(s), the plan that was generated by SQL Server will be stored in the plan cache. Even when the procedure is executed with different parameters, the same plan will be used. Let’s assume that data in table is not evenly distributed and one parameter value retrieves 10 rows while another parameter retrieves 10000. In such a case, we want SQL Server to use the optimum plan for data retrieval, but since the procedure is parameterized, it will use the same plan it first compiled. 

In the below example, I will demonstrate parameter sniffing. I am going to run two queries to show you the behavior, using the WideWorldImporters database on SQL Server 2016. The two queries are:

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

In the first query, SQL Server thinks that for "customerID 401", an index scan will be the more appropriate option, instead of using an Index Seek and key lookup.

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

exec GetCustomerTransaction 401

Look at the execution plan for customerID 401.

For "customerID 401", 23,233 rows have been returned. Considering the amount of data being retrieved, SQL Server chose to use an Index scan. Let's use another parameter for the same procedure

exec GetCustomerTransaction 976

For "customerID 976", only 250 rows are returned, but SQL Server is still performing an Index Scan. Also, see the difference between estimated number of rows and actual number of rows in the image above.

After reviewing this, the first question that came to my mind was: 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, you may need to do something to prevent it.