How SQL Server Optimizer Calculates the Estimated Row Count

This post can help you out in understanding “How SQL Server Optimizer Calculates the Estimated Row Count”. Once query is submitted, query optimizer takes the help from Histogram (Cardinality Estimates) statistics and builds the execution plan. Now we’ll see how histogram helps optimizer to get the estimated row count. Histogram contains 5 columns:

RANGE_HI_Key: Top data distributed values based on index or previous executed search conditions.

RANGE_Rows: When optimizer can’t find the exact match but nearby values. It captures the number of rows between the given nearby values.

EQ_ROWS: Number of rows exactly matching the search condition.

DISTINCT_Range_Rows: When optimizer can’t find the exact match but nearby values. It captures the number of distinct rows between the given nearby values.

AVG_RANGE_ROWS: When optimizer can’t find the exact match it still gets the estimated row count by using the formula: [Range_Rows]/[Distinct_Range_Rows]

Create a Table and Insert Data:

--Create a new table

CREATE TABLE Sales_Test (

OrderID INT IDENTITY(1,1) NOT NULL,

ProductID SMALLINT NOT NULL,

OrderQty SMALLINT NOT NULL,

UnitPrice MONEY NOT NULL)

--Create a clustered index on OrderID

CREATE CLUSTERED INDEX CL_IX_OrderID ON Sales_Test(OrderID);

--Create a Non-Clustered index on ProductID

CREATE NONCLUSTERED INDEX NON_CL_IX_ProductID ON Sales_Test(ProductID);

-- Insert Data from the table AdventureWorks2014.SALES.SalesOrderDetail

INSERT INTO Sales_Test

SELECT ProductID, OrderQty, UnitPrice

FROM SALES.SalesOrderDetail

GO 5

View Histogram:

Continue reading →