Databases Reference
In-Depth Information
By looking at the actual number of rows, you can see that the Index Scan operator is
reading 19,614 rows from the database and sending them to the Hash Aggregate
operator. The Hash Aggregate operator is, in turn, performing some operation on this
data and sending 575 records to its parent, which you can see by placing the mouse
pointer over the arrow between the Hash Aggregate and the SELECT icon.
Basically, in this instance, the Index Scan operator is reading all 19,614 rows from an
index, and the Hash Aggregate is processing these rows to obtain the list of distinct cities,
of which there are 575, which will be displayed in the Results window in Management
Studio. Notice, also, how you can see the estimated, as well as the actual, number of rows;
this is the Query Optimizer's cardinality estimation for this operator. Comparing the
actual and the estimated number of rows can help you to detect cardinality estimation
errors, which can affect the quality of your execution plans, as will be discussed in
Chapter 3
,
Statistics and Cost Estimation
.
To perform their job, physical operators implement at least the following three methods:
Open()
, which causes an operator to be initialized,
GetRow()
to request a row from
the operator, and
Close()
to shut down the operator once it has performed its role. An
operator can request rows from other operators by calling their
GetRow()
method. Since
GetRow()
produces just one row at a time, the actual number of rows displayed in the
execution plan is also the number of times the method was called on a specific operator,
and an additional call to
GetRow()
is used by the operator to indicate the end of the result
set. In the previous example, the Hash Aggregate operator calls the
Open()
method once,
GetRow()
19,615 times and
Close()
once on the Index Scan operator.
In addition to learning more about the data flow, you can also hover the mouse pointer
over an operator to get more information about it. For example, Figure 1-4 shows infor-
mation about the Index Scan operator; notice that it includes, among other things, data
on estimated costing information like the estimated I/O, CPU, operator and subtree
costs. You can also see the relative cost of each operator in the plan as a percentage of the
overall plan, as shown in Figure 1-2. For example, the cost of the Index Scan is 52% of the
cost of the entire plan.
