Obtaining Statement-Level Query Plans

Obtaining Statement-Level Query Plans

  • Comments 7

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="http://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="http://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)),

                       statement_start_offset/2,

                       1+      case when statement_end_offset = -1

                              then LEN((select text from master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2

                               else statement_end_offset/2 - statement_start_offset/2

                              end)

        ,3000)

, '[','[[]') + '%'

 

If you click on the result column statement_plan, the XML window will open revealing the plan.  Use File / Save to save that XML as a graphical showplan by giving it the extension .SQLPlan.   Double-clicking on that saved file will give you the graphical showplan window.

 

Even more useful, the function can be joined to the view sys.dm_exec_query_stats that reports per-statement IO, CPU and duration statistics.   For example, to find the plans for the 10 queries with longest average durations:

 

select pln.*, req.* from

(select top 10 * from sys.dm_exec_query_stats

order by total_elapsed_time/execution_count desc ) 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)),

                       statement_start_offset/2,

                       1+      case when statement_end_offset = -1

                              then LEN((select text from master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2

                              else statement_end_offset/2 - statement_start_offset/2

                              end)

        ,3000)

, '[','[[]') + '%'

 

And of course, you can also obtain a listing of every separate statement along with its plan and cost in a specific cached SP or Batch by querying the function with a specific plan_handle, eg:

 

select * from statement_level_query_plan(0x. . .)

 

-- Stuart Ozer

Leave a Comment
  • Please add 5 and 8 and type the answer here:
  • Post