Databases Reference
In-Depth Information
Just as before, the value 897 does not matter; any other value will give you the same
estimated number of rows and execution plan. However, this time the Query Optimizer
is not able to use the density information and instead it is using the standard guess of 30%
selectivity for inequality comparisons. That means that the estimated number of rows is
always 30% of the total number of records for an inequality operator and, in this case, 30%
of 121,317 is 36,395.1, as shown in Figure 3-3.
Figure 3-3:
Cardinality estimation example using a 30% guess.
However, the use of local variables in a query limits the quality of the cardinality estimate
when using the density information with equality operators. Worse, local variables result
in no estimate at all when used with an inequality operator, which results in a guessed
percentage. For this reason, local variables should be avoided in queries, and parameters
or literals should be used instead. When parameters or literals
are
used, the Query
Optimizer is able to use the histogram, which will provide better quality estimates than
the density information on its own.
As it happens, the last section of the
DBCC
SHOW_STATISTICS
output is the histogram,
which I will now explain.
