Database Reference
In-Depth Information
Figure 7-4.
Select operator properties
•
An operator detail shows both physical and logical operation types at the top. Physical
operations represent those actually used by the storage engine, while the logical operations
are the constructs used by the optimizer to build the estimated execution plan. If logical and
physical operations are the same, then only the physical operation is shown. It also displays
other useful information, such as row count, I/O cost, CPU cost, and so on.
•
Reading through the properties on many of the operators can be necessary to understand how
a query is being executed within SQL Server in order to better know how to tune that query.
Identifying the Costly Steps in an Execution Plan
The most immediate approach in the execution plan is to find out which steps are relatively costly. These steps are the
starting point for your query optimization. You can choose the starting steps by adopting the following techniques:
•
Each node in an execution plan shows its relative estimated cost in the complete execution
plan, with the total cost of the whole plan being 100 percent. Therefore, focus attention on the
nodes with the highest relative cost. For example, the execution plan in Figure
7-2
has one step
with 59 percent estimated cost.
•
An execution plan may be from a batch of statements, so you may also need to find the most
costly estimated statement. In Figure
7-2
you can see at the top of the plan the text “Query 1.”
In a batch situation, there will be multiple plans, and they will be numbered in the order they
occurred within the batch.
