Understanding Parameter Sniffing in SQL Server

For example, I am executing the below query by providing 3 different values to the ProductID column and see the execution plans of these. There is no change in the query except the value that we are providing to the ProductID column and this ProductID column already has a non-clustered index. I am going to execute the same query with 3 different results. So What query optimizer would do is it will compile each time and produce a new query plan for each individual queries which has different values and place it in the cache plan of the buffer pool. I am executing the queries with actual execution plans(Include Actual Execution plan)

The 897 query produced two rows and see the execution plan.

SELECT SalesOrderDetailID, OrderQty

FROM Sales.SalesOrderDetail

WHERE ProductID = 897;

Now I am running the same query with different values, this time there are 257 rows got affected, but query optimizer used the same query plan that is used for 897 value. So obviously query optimizer used the cached plan that is already used for 897 value(see above query). There is no difference here in the execution plan

SELECT SalesOrderDetailID, OrderQty

FROM Sales.SalesOrderDetail

WHERE ProductID = 945;

Now I am running the same query with different values, this time the value is 870 and the query returned 4688 rows so this time query optimizer generated a new query plan for value 870 with suggestive nonclustered index details. At this time Query optimize thought Index Scan is faster than Index seek and Key Lookup operators.

SELECT SalesOrderDetailID, OrderQty

FROM Sales.SalesOrderDetail

WHERE ProductID = 870;

Now we will see how this query behaves when we Parameterised. So I am creating a stored procedure like mentioned below.

CREATE PROCEDURE  OrderQtySalesOrderDetailIDInfo

@ProductID INT

AS

SELECT SalesOrderDetailID, OrderQty

FROM Sales.SalesOrderDetail

WHERE ProductID = @ProductID;

I have created an above-stored procedure and want to execute the stored procedure with ProductID value as 870 where we have 4688 rows got affected. Since it is the same value Query optimizer used the same query plan which is already there in the cache plan.

EXEC OrderQtySalesOrderDetailIDInfo @ProductID=870

Now I am going to execute that same stored procedure with productID 897 where we have two rows got affected. If we observe here Query optimizer used the same query plan that is used for product id value 870 where indexes are required and it is ignored first execution plan(see first query and execution plan) where you can also retrieve these two rows with  Index seek and Key lookup operators and above all to bring two rows it scanned 4688 rows.

EXEC OrderQtySalesOrderDetailIDInfo @ProductID=897

Why this happened because Parameter Sniffing is in Action.  One value is stored in the execution plan, and that is used to create the plan, regardless of what value is passed to the product id.

 We can verify this by right-clicking the execution plan and selecting “Show Execution Plan XML”. In the XML, I search for “ParameterCompiledValue”. I find the following line.

The compiled value is 870. Until the stored procedure is recompiled, this is the value that will be used.

So overcome this we will RECOMPILE the stored procedure and execute it WITH RECOMPILE option.

ALTER PROCEDURE  OrderQtySalesOrderDetailIDInfo

       @ProductID INT

       WITH RECOMPILE

       AS

       SELECT SalesOrderDetailID, OrderQty

       FROM Sales.SalesOrderDetail

       WHERE ProductID = @ProductID;

If you see now the stored procedure execution plan it has taken again Index Seek and Keylookup operators instead of index recommendation that is taken for value 870