Finding All Plans/SQL Statements that executed in Parallel

     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

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.