Database Reference
In-Depth Information
•
Nested loop join between the output of the previous join and the
Purchasing.PurchaseOrderDetail
table that was also the outer table
Nested loop join between the output of the previous join and the
•
Production.Product
table with
Production.Product
as the outer table
Nested loop join between the previous join and the
•
HumanResources.Employee
table with
the
HumanResource.Employee
table as the outer table
Additional processing:
•
•
Constant scan to provide a placeholder for the
@LastName
variable's
LIKE
operation
Compute scalar that defined the constructs of the
•
@LastName
variable's
LIKE
operation,
showing the top and bottom of the range and the value to be checked
•
Compute scalar that combines the
FirstName
and
LastName
columns into a new column
•
Compute scalar that calculates the
LineTotal
column from the
Purchasing.PurchaseOrderDetail
table
Compute scalar that takes the calculated
•
LineTotal
and stores it as a permanent value in
the result set for further processing
All this information is available by browsing the details of the operators exposed in the properties sheet from the
graphical execution plan.
Identifying the Costly Steps in the Execution Plan
Once you understand the execution plan of the query, the next step is to identify the steps estimated as the most
costly in the execution plan. Although these costs are estimated and don't reflect reality in any way, they are the
only numbers you will receive that measure the function of the plan, so identifying, understanding, and possibly
addressing the most costly operations can result in massive performance benefit. You can see that the following are
the two costliest steps:
•
Costly step 1
: The clustered index scan on the
Purchasing.PurchaseOrderHeader
table is
36 percent.
•
Costly step 2
: The hash match join operation is 32 percent.
The next optimization step is to analyze the costliest steps so you can determine whether these steps can be
optimized through techniques such as redesigning the query or indexes.
Analyzing the Processing Strategy
While the optimizer completed optimizing the plan, which you know because the reason for early termination of the
optimization process was “Good Enough Plan Found,” that doesn't mean there are not tuning opportunities in the
query and structure. You can begin evaluating it by following the traditional steps.
Costly step 1 is a clustered index scan. Scans are not necessarily a problem. They're just an indication that a
full scan of the object in question, in this case the entire table, was less costly than the alternatives to retrieve the
information needed by the query.
