How Local Variable or Optimize for Unknown Resolve Parameter Sniffing

In my last post , we have gone through the parameter sniffing  and possible solutions for parameter sniffing. In the possible solutions except the local variable and Optimize For Unknown are very straight forward solution and we know how they helps us to resolve the issue.In this post we will see how local variable and option for unknown are resolving the parameter sniffing issue.

If the parameter values are known while compiling the stored procedure , the optimizer use the statistics histogram and generate the best plan for the parameters.When we define local variable and use that in the query, SQL server will not be able use the parameter values to find the optimal value. In this scenario optimizer use density vector information of the statistics and it will generate same execution plan for all input parameter. Let us see how it will work.


Below statement returns returns 13 records by doing index seek and key lookup operation.This plan is generated based on the estimation that, the query will return 44.5 records. The query optimizer done the estimation based on the histogram.

SELECT * FROM Sales.SalesOrderDetail WHERE productid =744

DBCC show_statistics('Sales.SalesOrderDetail','IX_SalesOrderDetail_ProductID')


A portion of the out put of the above query will looks like below

The estimated number of rows is calculated based on the EQ_ROWS and AVG_RANGE_ROWS. In this example, the parameter value 744 is not matching with RANGE_HI_KEY and optimizer took AVG_RANGE_ROWS values of 747 to calculate the estimated number of rows. The execution plan will be same if you convert this to a procedure.

Let us see how it will work with procedure with local variable

  CREATE PROCEDURE get_SalesOrderDetail 
(
   @ProductId INT
)AS
   DECLARE @L_ProductId INT
   SET @L_ProductId =@ProductId
   SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @L_ProductId 


If you execute this procedure with parameter value 744, the execution plan will will looks like below.

EXEC get_SalesOrderDetail 744

This time optimizer gone for index scan under the estimation that the query will return 456 records.As we have defined the local variable, the parameter value is not available at compilation time and optimizer used the density vector to estimate the number of row. The value of estimated number of rows will be same in execution plan of this procedure with any parameter value and hence the execution plan.

Let us see how optimizer calculating the estimated number of rows in this case. As the parameter value is not available at the time of optimization, it assumes that records are distributed uniformly. In the SalesOrderDetail table we have 266 distinct value for Productid and the total number of records is 121317.If you divide total number of records with number of distinct values of productid , you will get 121317/266=456.07 which is same as estimated number of rows. All these data required for the calculation are available in the statistics.The total number of records is available in the first sections. The density value 0.003759399 is available in the second section which is equivalent to 1/266. So the estimated number of rows =121317X0.003759399 = 456.079.

You can see the same execution plan if we change this procedure with optimize for unknown as given below

ALTER PROCEDURE get_SalesOrderDetail (
   @ProductId INT)AS
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductId OPTION (OPTIMIZE FOR UNKNOWN)

Hope you enjoyed reading this post.If you liked this post, do like my FaceBook Page: http://www.facebook.com/PracticalSqlDba