如何提取查询执行计划的CXPACKET等待时间？为何XML存在差异？

两种执行计划XML存在差异的原因 #

• 手动右键查看的是实际执行计划：该计划包含查询运行时的真实执行数据，包括等待时间、实际扫描行数、CPU耗时等单次执行的具体统计信息，这些数据不会被存入SQL Server的计划缓存。
• 通过sys.dm_exec_query_stats等DMV获取的执行计划：来自SQL Server的计划缓存，属于预估执行计划（或缓存的计划模板），仅包含查询优化器生成的执行逻辑框架，不会保存单次执行的运行时统计（比如等待时间），因此无法找到CXPACKET等待项。

正确提取CXPACKET等待时间的方法 #

1. 捕获实际执行计划XML并解析 #

通过开启SET STATISTICS XML ON获取包含运行时统计的实际执行计划XML，再用XQuery提取CXPACKET等待时间：

-- 开启实际执行计划XML输出
SET STATISTICS XML ON;

-- 执行目标查询
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY CarrierTrackingNumber DESC
OPTION (RECOMPILE);

-- 关闭输出
SET STATISTICS XML OFF;

将返回的XML内容复制后，使用以下SQL解析：

DECLARE @actual_plan XML = '--- 此处粘贴实际执行计划XML内容 ---';

SELECT 
    wait.value('@WaitType', 'NVARCHAR(128)') AS WaitType,
    wait.value('@WaitTimeMs', 'BIGINT') AS WaitTimeMs,
    wait.value('@SignalWaitTimeMs', 'BIGINT') AS SignalWaitTimeMs
FROM @actual_plan.nodes('//WaitStats/Wait') AS ws(wait)
WHERE wait.value('@WaitType', 'NVARCHAR(128)') = 'CXPACKET';

2. 使用扩展事件跟踪 #

创建扩展事件会话，精准捕获当前会话的CXPACKET等待事件：

-- 创建扩展事件会话
CREATE EVENT SESSION TrackCXPACKETWaits ON SERVER 
ADD EVENT sqlos.wait_completed(
    WHERE wait_type = N'CXPACKET'
    AND sqlserver.session_id = @@SPID) -- 仅跟踪当前会话
ADD TARGET package0.event_file(SET filename=N'C:\Temp\CXPACKET_Trace.xel') -- 指定存储路径
WITH (STARTUP_STATE=OFF);

启动会话、执行查询后，停止会话并读取事件数据：

-- 启动会话
ALTER EVENT SESSION TrackCXPACKETWaits ON SERVER STATE = START;

-- 执行目标查询
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY CarrierTrackingNumber DESC
OPTION (RECOMPILE);

-- 停止会话
ALTER EVENT SESSION TrackCXPACKETWaits ON SERVER STATE = STOP;

-- 查询捕获的等待数据
SELECT 
    event_data.value('(event/data[@name="wait_type"]/text)[1]', 'NVARCHAR(128)') AS WaitType,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'BIGINT')/1000 AS WaitTimeMs, -- 转换为毫秒
    event_data.value('(event/data[@name="signal_duration"]/value)[1]', 'BIGINT')/1000 AS SignalWaitTimeMs
FROM (
    SELECT CONVERT(XML, event_data) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\Temp\CXPACKET_Trace*.xel', NULL, NULL, NULL)
) AS x;

-- 可选：删除会话
DROP EVENT SESSION TrackCXPACKETWaits ON SERVER;

3. 会话级等待统计差值法 #

通过对比查询执行前后的会话等待统计，计算CXPACKET等待时间的增量：

-- 记录执行前的CXPACKET等待时间
SELECT wait_time_ms AS Pre_CXPACKET_Wait
INTO #PreWaitStats
FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@SPID AND wait_type = 'CXPACKET';

-- 执行目标查询
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY CarrierTrackingNumber DESC
OPTION (RECOMPILE);

-- 计算执行后的等待时间增量
SELECT 
    s.wait_time_ms - ISNULL(p.Pre_CXPACKET_Wait, 0) AS CXPACKET_WaitTime_Ms
FROM sys.dm_exec_session_wait_stats s
LEFT JOIN #PreWaitStats p ON 1=1
WHERE s.session_id = @@SPID AND s.wait_type = 'CXPACKET';

DROP TABLE #PreWaitStats;

注意：此方法需确保当前会话在查询执行期间无其他并行操作，避免干扰统计结果。

内容的提问来源于stack exchange，提问作者backfischxx