Databases Reference
In-Depth Information
This is the resulting output, with the
EstimateRows
column manually moved just after
the
Rows
column, and edited to fit the page:
Rows EstimateRows Executes StmtText
------ ------------ -------- ----------------------------------------
772 36395.1 1 SELECT * FROM [Sales].[SalesOrderDetail]
772 36395.1 1 |--Filter(WHERE:([AdventureWorks] .[Sa
0 121317 0 |--Compute Scalar(DEFINE:([Advent
0 121317 0 |--Compute Scalar(DEFINE:([A
121317 121317 1 |--Clustered Index Scan
Listing 3-47.
Using this output, you can easily compare the actual number of rows, shown on the
Rows
column, against the estimated number of records, as shown on the
EstimateRows
column, for each operator in the plan.
Because each operator relies on previous operations for its input, cardinality estimation
errors can propagate exponentially throughout the query plan. For example, a cardinality
estimation error on a Filter operator can impact the cardinality estimation of all the other
operators in the plan that consume the data produced by that operator. If your query is
not performing well and you find cardinality estimation errors, check for problems like
missing or out-of-date statistics, very small samples being used, correlation between
columns, use of scalar expressions, guessing selectivity issues, and so on.
Recommendations to help with these issues have been provided throughout this
chapter and include things like using the auto-create and auto-update statistics default
configurations, updating statistics using
WITH
FULLSCAN
, avoiding local variables in
queries, using computed columns, and considering multi-column or filtered statistics,
among other things. That's a fairly long list, but it should help convince you that you are
already armed with pragmatically useful information.
