Databases Reference
In-Depth Information
Summary
In this chapter, we have seen how statistics are used by SQL Server to estimate the
cardinality as well as the cost of operators and execution plans. The most important
elements of a statistics object, namely the histogram, the density information, and string
statistics, were introduced and explained. Examples of how to use histograms were
shown, including queries with equality and inequality operators and both
AND
'ed and
OR
'ed predicates. The use of density information was shown in
GROUP
BY
operations, and
in cases when the Query Optimizer is not able to use a histogram, such as in the case of
local variables.
Maintenance of statistics was also explained, with some emphasis on how to proactively
update statistics to avoid delays during query optimization, and how to improve the
quality of statistics by scanning the entire table instead of a default sample. We also
discussed how to detect cardinality estimation errors, which can negatively impact the
quality of your execution plans, and we looked at recommendations on how to fix them.
