Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 262

RetrievingaQueryPlanfromaPlanCache
In SQL Server 2005, you have the ability to retrieve plans from SQL Server's plan cache. Using DMVs
and functions, you can actually retrieve a plan that was used by SQL Server. This query will retrieve all
the SQL statements and query plans you might want to troubleshoot.

Select st.text, qp.query_plan
From sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp

The Text column is the SQL statement, and the query_plan column is an XML representation of the plan,
just like it was generated from SHOWPLAN_XML. Next, save the query_plan column in a file with an
extension of .sqlplan. You can now use SQL Server Management Studio and open the file. You will see a
graphical plan!

This is very useful because you can get a plan that was actually used by SQL Server to execute the query.
Many times you'll be tasked with troubleshooting a query that has sporadic performance
problems. This is especially true if you have stored procedures that accept parameters and in turn use the
parameters as filters on a select statement. When SQL Server generated a plan for this procedure it may
not have created a plan that works well with all parameter values. Therefore, sometimes the procedure
will run fine, and other times it won't. By retrieving the plan from cache, you'll be able find out what
SQL Server actually did.

With the plan handle, you can examine the graphical query execution plan, the T-SQL text, or certain
attributes about the plan. Table 9-8 describes the different TVFs you'll need for interpretation of these
plans.

You'll need VIEW SERVER STATE permission to use these functions and DMVs.

Table 9-8: DMV List for Retrieving a Plan from the Plan Cache

Command Returns Cached Plan

sys.dm_exec_query_plan XML Showplan information

Sys.dm_exec_sql_text T-SQL statement

Sys.dm_exec_plan_attributes Set of Code-Value pairs containing cached counters and values.

Another great place to look at performance related to cached plans is in the sys.dm_exec_query_stats
DMV. The result set returned from this DMV contains counters for worker thread time, all I/O activity,
common language runtime counters, and general time counters. You can use this query to monitor all of
this in one shot and translate the plan_handle and sql_handle values.

SELECT qplan.Query_Plan, stext.Text, qstats.*, plns.*
FROM sys.dm_exec_cached_plans plns
JOIN sys.dm_exec_query_stats as qstats