999
Chapter 44: Interpreting Query Execution Plans
44
Set Showplan must be the only statement in the batch.Set Showplan comes is three fl avors: all, text, and XML:■ (^) Showplan_all displays the operators as a result set. It exposes the same informa-
tion as the graphical execution plan. The executing statement is returned in the
fi rst 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 (fi rst col-
umn) 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 fi nd the Showplan options by
selecting Execution ➪ Advanced.
■ Showplan_xml displays more detail than any other method of viewing the execu-
tion plan, and it offers the benefi t of storing and displaying unstructured data, so
it can display additional information that may not pertain to all execution plans.
For example, in the
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 Profi ler’s Execution Plans
Within the Performance event category, SQL Server Profi ler includes several Showplan
events. The Showplan XML event includes the XML for the query execution plan, which SQL
Profi ler 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 Profi ler, 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 Profi le event, you can
choose to Extract Event Data. This enables you to save the plan as a .sqlplan fi le. 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.
c44.indd 999c44.indd 999 7/31/2012 10:19:27 AM7/31/2012 10:19:27 AM
http://www.it-ebooks.info