Cardinality estimation errors

Cardinality estimation errors can lead to the Query Optimizer making poor choices as to how best to execute a query and, therefore, to badly performing execution plans. Fortunately, you can easily check whether you have cardinality estimation errors by comparing the estimated against the actual number of rows, as shown in graphical or XML execution plans, or by using the SET STATISTICS PROFILE statement. In the following query, we will show you how to use the SET STATISTICS PROFILE statement with one of our previous examples, where SQL Server is making a blind guess regarding the selectivity of certain columns:

SET STATISTICS PROFILE ON

GO

SELECT * FROM Sales.SalesOrderDetail

WHERE OrderQty * UnitPrice > 10000

GO

SET STATISTICS PROFILE OFF

GO

This is the resulting output, with the EstimateRows column manually moved just after the Rows column, and edited to fit the page:

Rows EstimateRows StmtText