Databases Reference
In-Depth Information
Chapter 3: Statistics and Cost
Estimation
The SQL Server Query Optimizer is a cost-based optimizer, and therefore the quality
of the execution plans it generates is directly related to the accuracy of its cost
estimations. In the same way, the estimated cost of a plan is based on the algorithms
or operators used, and their cardinality estimations. So, to correctly estimate the cost of
an execution plan, the Query Optimizer needs to estimate, as precisely as possible, the
number of records returned by a given query.
During query optimization, SQL Server explores many candidate plans, estimates their
relative costs and selects the most efficient one. As such, incorrect cardinality and cost
estimation may cause the Query Optimizer to choose inefficient plans which can have a
negative impact on the performance of your database.
In this chapter, I'll discuss the statistics used by the Query Optimizer. Statistics contain
three major pieces of information: the histogram, the density information, and the string
statistics, all of which help with different parts of the cardinality estimation process. I
will show you how statistics are created and maintained, and how they are used by the
Query Optimizer. I will also provide you with information on how to detect cardinality
estimation errors that can negatively impact the quality of your execution plans, as well as
recommendations on how to fix them. The chapter ends with an overview of the costing
module, which estimates the I/O and CPU cost for each operator, to finally obtain the
total cost of the plan.
