997
Chapter 44: Interpreting Query Execution Plans
44
Query Optimizer can estimate the number of rows for each operator based on statistics and
use that estimate in determining the plan.This is a very important concept when it comes to performance tuning. The Query
Optimizer relies heavily on statistics, and if the statistics are skewed, then a non-optimal
plan may be chosen.After the plan is executed, the query processor adds to the plan the actual number of rows
processed by each operation.The estimated query execution plan may be viewed by selecting the query in the Query
Editor and either clicking the Display Estimated Execution Plan button on the toolbar,
selecting Query ➪ Display Estimated Execution Plan. The actual plan may be viewed in
a similar fashion. To enable viewing the actual execution plan, choose Query ➪ Include
Actual Execution Plan.Because the query isn’t actually executed when viewing the estimated execution plan, the
resulting plan should display in the Execution Plan tab rather quickly.Reading the Execution Plan
The data fl ow of an execution plan is typically from right to left, top to bottom, as
shown in Figure 44-1. Each operator is presented as an icon, otherwise known as a plan
operator or iterator. The graphical execution plan is an interactive display, which enables
you to hover the mouse over the operators and connections to discover the following:■ (^) Mousing over the plan operators causes a dialog box to appear containing detailed
information about the operator, including an operator description, different cost
fi gures, number of estimated or actual rows involved, and the portion of the query
handled by the operation.
■ (^) Mousing over a connector line presents detailed information about how much data
is transferred between operators.
■ (^) The Property window also presents detailed information about any operator or con-
nection between operators.
The display may be zoomed or sized to fi t using the right-click context menu.
To walk through the query execution plan shown in the following Figure 44-1, follow these steps:
- In the upper-right corner of the plan, the index seek operation fi nds every row with
ProductID = 757 using an index seek operation against the WorkOrder
.IX_WorkOrder_ProductID nonclustered index. - The nested-loop operation receives every row from the index seek and asks for
those same rows from the clustered index, calling the key lookup operation. You
can ignore the compute scalar operation because it handles only a type conversion. - The nested-loop assembles, or joins, the data from the index seek and key lookup and
passes the data to the select operation, which returns the correct columns to the client.
c44.indd 997c44.indd 997 7/31/2012 10:19:27 AM7/31/2012 10:19:27 AM
http://www.it-ebooks.info