Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 249

Table 9-6: Operator Details

ToolTip item Description

Physical
Operation

The physical operator used, such as Hash Join or Nested Loops. Physical
operators displayed in red indicate that the query optimizer has issued a
warning, such as missing column statistics or missing join predicates. This can
cause the query optimizer to choose a less efficient query plan than otherwise
expected. When the graphical execution plan suggests creating or updating
statistics, or creating an index, the missing column statistics and indexes can be
immediately created or updated using the shortcut menus in SQL Server
Management Studio Object Explorer.

Logical
Operation

The logical operator that matches the physical operator, such as the Inner Join
operator. The logical operator is listed after the physical operator at the top of
the ToolTip.

Estimated
Row Size

The estimated size of the row produced by the operator (bytes).

Estimated
I/O Cost

The estimated cost of all I/O activity for the operation. This value should be as
low as possible.

Estimated
CPU Cost

The estimated cost of all CPU activity for the operation.

Estimated
Operator Cost

The cost to the query optimizer for executing this operation. The cost of this
operation as a percentage of the total cost of the query is displayed in
parentheses. Because the query engine selects the most efficient operation to
perform the query or execute the statement, this value should be as low as
possible.

Estimated
Subtree Cost

The total cost to the query optimizer for executing this operation and all
operations preceding it in the same subtree.

Estimated
Number of
Rows

The number of rows produced by the operator.

I'm not going to cover all the operators listed in these tables. However, the most common ones will be
addressed. Those include the various join operators, spool operators, seek and scan operators, and lookup
operators.

JoinOperators
SQL Server employs three types of join strategies. The first is the Nested Loop Join Operator. Prior
to SQL Server version 7, this was the only join operator available. The other two are known as merge
joins and hash joins . When the query optimizer encounters a join it will evaluate all three join types and
select the strategy with the lowest cost. Note that the tables to be joined are known as the join inputs.