You have several options for
viewing execution plans. As well as
viewing them via Dynamic Management Views and capturing them in SQL
Server Profiler.
- Management Studio can display the estimated or actual query execution plan graphically, through XML, or via a result set.
- The Showplan directive can return the estimated query plan as a message or result set.
- The STATISTICS PROFILE directive can return the actual query plan as a message or result set.
- SQL Profiler can capture the query execution plan as plain text or as XML that can be viewed in graphic form.
- Execution plans in the plan cache may be viewed using dynamic management views (DMVs).
In addition, many of the preceding methods enable
an execution plan to be saved as an XML file, which can be opened later
using Management Studio.
Estimated versus Actual Execution Plans
SQL Server can return the estimated
query execution plan before the query is executed, or it can return the
actual query execution plan with the results of the query.
The difference between the estimated and the
actual typically isn't the plan; the sequence of physical operations
are often the same. The difference in the estimated versus actual plans
are the number of rows returned by each operator. Before the query is
executed, the 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 flow of an execution plan is typically from right to left, top to bottom, as shown in Figure 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 figures, 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 connection between operators.
The display may be zoomed or sized to fit using the right-click context menu.
To walk through the query execution plan shown in the following Figure 1, follow these steps:
1. In the upper-right corner of the plan, the index seek operation finds every row with ProductID = 757 using an index seek operation against the WorkOrder.IX_WorkOrder_ProductID nonclustered index.
2. 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.
3. 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.
Key pieces of information on the query plan follow:
- The type of operation. Key operations are listed later in Table 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 file (.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 directives enable you to view
the execution plans in different formats. Showplan is the estimated
plan, whereas STATISTICS PROFILE is the actual plan.
Set Showplan must be the only statement in the batch.
Set Showplan comes is three flavors: all, text, and XML:
- Showplan_all
displays the operators as a result set. It exposes the same information
as the graphical execution plan. The executing statement is returned in
the first row, and every operator is returned as subsequent rows. (This
is a deprecated feature and will be eliminated in a future version.)
- Showplan_text is similar to showplan_all except that the executing statement and the operations are in separate result sets and only the stmt text (first column) displays.
- The showplan_text option, along with the set statistics
options, may also be toggled graphically within Query Editor. Use the
context menu's Query Options command to open the Query Properties, and
you can find the Showplan options by selecting Execution ? Advanced.
- Showplan_xml
displays more detail than any other method of viewing the execution
plan, and it offers the benefit of storing and displaying unstructured
data, so it can display additional information that may not pertain to
all execution plans. For example, in the <Statement> element, Showplan_xml displays the Query Optimizer optimization level, or the reason why the Query Optimizer returned this execution plan.
- For the XML version of Showplan, the Include Actual Execution Query
Editor option must be off. In addition, if the query results are set to
grid, then the grid offers a link to open the XML using the browser.
SQL Profiler's Execution Plans
Within the Performance event category,
SQL Server Profiler includes several Showplan events. The Showplan XML
event includes the XML for the query execution plan, which SQL Profiler
displays in a graphical form. It includes the same features as the
Query Editor to mouse over the operation to see more properties and
zoom the display.
You can save the plan with SQL Profiler, but it's
well hidden: If you right-click anywhere in the upper pane on the line
of a Showplan XML or Showplan XML Statistics Profile event, you can
choose to Extract Event Data. This enables you to save the plan as a .sqlplan file. Cool add!
Examining Plans using Dynamic Management Views
(DMVs) previously introduced with SQL Server 2005, provide an excellent
window into SQL Server's internals. Three of the DMVs expose the query
execution plans currently in the cache:
- sys.dm_exec_cached_plans: Returns the plan type, memory size, and usecounts.
- sys.dm_exec_query_stats: Returns several aggregate execution statistics (for example, last_execution_time, max_elapsed_time).
- sys.dm_exec_requests: Returns plans that are currently executing.
- sys.dm_exec_procedure_stats: Returns aggregated execution statistics for stored procedures.
Each of these previous DMVs returns a plan handle
(binary identifier of the query execution plan in memory) that can be
passed to one of the following dynamic management functions with a cross apply to extract the query text or the query execution plan:
- sys.dm_exec_query_plan(plan_handle):
Returns the query execution plan in XML. For some complex queries, if
the XML nesting level is greater than 128, this method of extracting
the query plan fails. Use the next method instead.
- sys.dm_exec_text_query_plan(plan_handle): Returns the query execution plan as a text Showplan.
- sys.dm_exec_sql_text(plan_handle): Returns the query SQL statement.
The code example in Figure 2 pulls together data from the DMVs to view the original SQL statements and query execution plans from the cache.
