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