Advertisements
Home > Dynamic Management Views/Functions > Finding All Plans/SQL Statements that executed in Parallel

Finding All Plans/SQL Statements that executed in Parallel

       WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
     SELECT   query_plan                                            AS CompleteQueryPlan
            , n.value('(@StatementText)[1]', 'VARCHAR(4000)')       AS StatementText
            , n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)')    AS StatementOptimizationLevel
            , n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
            , n.query('.')                                          AS ParallelSubTreeXML
       FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')     AS qn(n)
      WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1

References: #21

Statement copied from The Rambling DBA: Jonathan Kehayias Blog

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: