Database Reference
In-Depth Information
Figure 7-2. Query execution plan
Execution plans show two different flows of information. Reading from the left side, you can see the logical flow,
starting with the SELECT operator and proceeding through each of the execution steps. Starting from the right side and
reading the other way is the physical flow of information, pulling data from the Clustered Index Scan operator first
and then proceeding to each subsequent step. Most of the time, reading in the direction of the physical flow of data is
more applicable to understanding what's happening with the execution plan, but not always. Sometimes the only way
to understand what is happening in an execution plan is to read it in the logical processing order, left to right. Each
step represents an operation performed to get the final output of the query. Some of the aspects of a query execution
represented by an execution plan are as follows:
If a query consists of a batch of multiple queries, the execution plan for each query will be
displayed in the order of execution. Each execution plan in the batch will have a relative
estimated cost, with the total cost of the whole batch being 100 percent.
Every icon in an execution plan represents an operator. They will each have a relative
estimated cost, with the total cost of all the nodes in an execution plan being 100 percent
(although inaccuracies in statistics, or even bugs in SQL Server, can lead to situations where
you see costs more than 100 percent, but these are uncommon).
Usually the first physical operator in an execution represents a data retrieval mechanism from
a database object (a table or an index). For example, in the execution plan in Figure 7-2 , the
three starting points represent retrievals from the SalesOrderHeader , SalesOrderDetail , and
Product tables.
Data retrieval will usually be either a table operation or an index operation. For example, in
the execution plan in Figure 7-2 , all three data retrieval steps are index operations.
Data retrieval on an index will be either an index scan or an index seek. For example, you can
see a clustered index scan, a clustered index seek, and an index scan in Figure 7-2 .
The naming convention for a data retrieval operation on an index is
[Table Name]. [Index Name] .
The logical flow of the plan is from left to right, just like reading a book in English. The data
flows from right to left between operators and is indicated by a connecting arrow between
the operators.
The thickness of a connecting arrow between operators represents a graphical representation
of the number of rows transferred.
 
Search WWH ::




Custom Search