Databases Reference
In-Depth Information
Cost Estimation
As we've established, the quality of the execution plans the Query Optimizer generates is
directly related to the accuracy of its costing estimates. Even when the Query Optimizer
is able to enumerate low cost plans, an incorrect cost estimation may result in the Query
Optimizer choosing inefficient plans, which can negatively impact the performance of
your database. During query optimization, the Query Optimizer explores many candidate
plans, estimates their cost, and then selects the most efficient one. So, in addition to being
accurate, cost estimation must also be efficient, since it is used multiple times during the
query optimization process.
Costs are estimated for any partial or complete plan; cost computation is done per
operator, and the total plan cost is the sum of the costs of all the operators in that plan.
The cost of each operator depends on its algorithm and the estimated number of records
it returns, and some operators, such as Sort or Hash Join, also consider the available
memory in the system. A high level overview of the cost of the algorithms for some of
the most used operators was included in
Chapter 2
,
The Execution Engine
.
So, each operator has an associated CPU cost, and some of them will also have some I/O
cost, and the cost of the operator as a whole is the sum of these costs. An operator like
a Clustered Index Scan has both CPU and I/O costs, whereas some other operators, like
Stream Aggregate, will only have a CPU cost. Since Microsoft does not publish how
these costs are calculated, I will show you a very basic example of how the cost of a plan
is estimated.
To show this in an example, let's look at the largest table in the
AdventureWorks
database. Run the following query and look at the estimated CPU and I/O costs for the
Clustered Index Scan operator, as shown in Figure 3-15:
SELECT
*
FROM
Sales
.
SalesOrderDetail
WHERE
LineTotal
=
35
Listing 3-58.
