Obtaining Statement-Level Query Plans
In workloads that involve expensive long-running queries and reports, or complex batch operations, you frequently want to see the SQL statements and associated query plans that are active on the system or that consume the most resources.
SQL Server 2005 makes available the query plans and SQL text associated with running queries or cached statements through the management views sys.dm_exec_requests and sys.dm_exec_query_stats respectively. Both of these views provide a plan_handle for the XML plan and a sql_handle for the query text, along with resource consumption and execution information. To view the plan or SQL text, you use the table-valued functions sys.dm_exec_query_plan(<plan_handle>) and sys.dm_exec_sql_text(<sql_handle>).
However, the plans and text returned for these handles include all of the queries in the SP or batch, rather than an individual sql statement or plan. Yet the views sys.dm_exec_requests and sys.dm_exec_query_stats contain a separate entry for each statement within an SP or Batch, along with metrics about that statement.
So how do you capture the individual statement and plan corresponding to a specific row in these tables? The only link provided in these tables to identify the SQL statement that is running, or a cached query of interest within the SP or Batch, are the columns statement_start_offset and statement_end_offset which isolate the substring containing the individual statement’s SQL.
Fortunately, using the XML parsing capabilities built-in to SQL2005, we can split a multi-statement query plan into separate fragments for each statement in a batch or SP. From each of these fragments we can also extract the corresponding SQL query string of the statement it applies to – since each plan fragment contains the SQL text as an element. And then we can join this single-statement SQL text to the substring identified by the offsets I mentioned above to retrieve just the plan of the specific SQL statement of interest.
To illustrate this parsing, consider the following table-value function that partially shreds sys.dm_exec_query_plan to generate a separate row for each SQL statement in a batch or SP. Each row contains both the SQL Text and Query Plan of statements for a given plan_handle : (Note: This function requires CTP16 or later versions of SQL2005)
-- statement_level_query_plan.sql ***********************************************
--
-- v1.3
-- Stuart Ozer
-- Microsoft Corporation
-- Provided AS-IS with no warranties
--
--
CREATE FUNCTION statement_level_query_plan(
@handle as varbinary(64) -- Handle for the overall query plan
)
RETURNS TABLE as
RETURN (
select
statement_nbr, -- Sequential number of statement within batch or SP
statement_type, -- SELECT, INSERT, UPDATE, etc
statement_subtree_cost, -- Estimated Query Cost
statement_estimated_rows, -- Estimated Rows Returned
statement_optimization_level, -- FULL or TRIVIAL
statement_text, -- Text of query
statement_plan -- XML Plan To view as a graphical plan
-- save the column output to a file with extension .SQLPlan
-- then reopen the file by double-clicking
from (
select
C.value('@StatementId','int') as statement_nbr,
C.value('(./@StatementText)','nvarchar(max)') as statement_text,
C.value('(./@StatementType)','varchar(20)') as statement_type,
C.value('(./@StatementSubTreeCost)','float') as statement_subtree_cost,
C.value('(./@StatementEstRows)','float') as statement_estimated_rows,
C.value('(./@StatementOptmLevel)','varchar(20)') as statement_optimization_level,
-- Construct the XML headers around the single plan that will permit
-- this column to be used as a graphical showplan.
-- Only generate plan columns where statement has an associated plan
C.query('declare namespace PLN="https://schemas.microsoft.com/sqlserver/2004/07/showplan";
if (./PLN:QueryPlan or ./PLN:Condition/PLN:QueryPlan)
then
<PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
{ ./attribute::* }
{ ./descendant::PLN:QueryPlan[1] }
</PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
else ()
') as statement_plan
from
sys.dm_exec_query_plan(@handle)
CROSS APPLY
-- This expression finds all nodes containing attribute StatementText
-- regardless of how deep they are in the potentially nested batch hierarchy
-- The results of this expression are processed by the Select expressions above
query_plan.nodes('declare namespace PLN="https://schemas.microsoft.com/sqlserver/2004/07/showplan";
/PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
as T(C)
) x
)
Besides delivering columns containing the statement-level text and XML plan, the function also extracts other useful information as columns such as estimates for the query cost and expected rowcount.
To use the function to view query plans, statements and execution details for all currently executing queries, we join the above function to the executing SQL text substrings obtained from sys.dm_exec_requests. The join is a bit complex because we have to take into account the fact that query strings embedded in the plan may have some spurious leading characters, and is also truncated while the query in sys.dm_exec_requests is not. So, the query to report all executing SQL statements and their plans is:
select pln.*, req.* from sys.dm_exec_requests as req
CROSS APPLY statement_level_query_plan(plan_handle) as pln
where statement_text like
'%' +
replace(
left(
substring((select text from master.sys.dm_exec_sql_text(sql_handle)),