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.

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`

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

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

CREATE PROCEDURE get_SalesOrderDetail

(

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

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.

(

` @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.

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

Copyright © 2002-2016 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.