查询并行计划的SQL语句

sys.dm_exec_query_plan以 XML格式返回计划句柄指定的批查询的显示计划，下面的语句就是通过查询XML获得并行计划信息。

SELECT TOP 10

p.*,

q.*,

qs.*,

cp.plan_handle

FROM

sys.dm_exec_cached_planscp

CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p

CROSS apply sys.dm_exec_sql_text(cp.plan_handle)AS q

JOIN sys.dm_exec_query_stats qs

ON qs.plan_handle= cp.plan_handle

WHERE

cp.cacheobjtype='Compiled Plan'AND

p.query_plan.value('declare namespacep="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

max(//p:RelOp/@Parallel)','float') > 0

OPTION (MAXDOP 1)

下面的可以用来查正在执行的语句：

---requests arerunning in parallel

select

r.session_id,

r.request_id,

max(isnull(exec_context_id, 0)) asnumber_of_workers,

r.sql_handle,

r.statement_start_offset,

r.statement_end_offset,

r.plan_handle

from

sys.dm_exec_requests r

join sys.dm_os_tasks t on r.session_id = t.session_id

join sys.dm_exec_sessions s onr.session_id =s.session_id

where

s.is_user_process= 0x1

group by

r.session_id, r.request_id,

r.sql_handle, r.plan_handle,

r.statement_start_offset, r.statement_end_offset

having max(isnull(exec_context_id, 0)) > 0