1000
Part VIII: Performance Tuning and Optimization
■ (^) sys.dm_exec_procedure_stats: Returns aggregated execution statistics for
stored procedures.
Each of these previous DMVs returns a plan handle (binary identifi er 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 44-2 pulls together data from the DMVs to view the original SQL
statements and query execution plans from the cache.
FIGURE 44-2
Using DMVs, you can view the SQL code and the query execution plan in the procedure
cache. Clicking the XML in the right-most column would open another tab with the graphical
view of the selected query execution plan.
c44.indd 1000c44.indd 1000 7/31/2012 10:19:28 AM7/31/2012 10:19:28 AM
http://www.it-ebooks.info