998
Part VIII: Performance Tuning and Optimization
FIGURE 44-1
Execution plans show the operators SQL Server uses to satisfy a query.Key pieces of information on the query plan follow:■ The type of operation. Key operations are listed later in Table 44-1.■ (^) The object, listed below the operator and in the pop-up information box, is the
actual index hit by the operation.
■ (^) The estimated number of rows, because the query optimizer uses the estimated
number of rows to choose the best query execution plan.
■ (^) The estimated operator cost and the estimated subtree cost are relative values used
by the query optimizer. When tuning a query, these are critical values to watch.
You can read the cost as cost times 1,000 — For example, .0051234 as 5, or .3255786
as 325, to make it easier to think through the plans.
You can also save plans to a plan fi le (.sqlplan) to be reexamined later. Re-opening a plan
opens the graphical execution plan. The context menu has a new option to edit the SQL
query, which opens the original SQL statement in a new Query Editor tab.
Using Showplan and STATISTICS PROFILE
In addition to the graphical execution plan, the Showplan and STATISTICS PROFILE
directives reveal the execution plan with some additional detail. Similar to how you can
view Estimated and Actual Execution plans graphically in Management Studio, these direc-
tives enable you to view the execution plans in different formats. Showplan is the esti-
mated plan, whereas STATISTICS PROFILE is the actual plan.
c44.indd 998c44.indd 998 7/31/2012 10:19:27 AM7/31/2012 10:19:27 AM
http://www.it-ebooks.info