如何通用提取SQL Server执行计划XML数据为表格结构？

1. 获取执行计划XML #

先拿到执行计划的原始XML内容，有两种常用方式：

• 在SSMS中执行查询后，右键点击执行计划，选择「显示执行计划XML」，复制完整的XML文本。
• 用T-SQL生成：执行SET SHOWPLAN_XML ON;后运行目标查询，结果集里的XML就是执行计划；或者用SET STATISTICS XML ON;执行查询，也能得到包含执行计划的XML结果。

2. 通用解析XML的T-SQL脚本 #

执行计划XML的结构会随查询变化，但核心执行节点都在//RelOp路径下。可以用递归CTE结合XQuery来遍历所有节点，提取关键数据并转换为表格：

DECLARE @PlanXML XML = '<替换为你的执行计划XML内容>';

WITH XmlNodes AS (
    -- 提取根级RelOp节点
    SELECT
        CAST('/RelOp[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS varchar(10)) + ']' AS varchar(500)) AS NodePath,
        n.value('@PhysicalOp', 'varchar(100)') AS PhysicalOp,
        n.value('@LogicalOp', 'varchar(100)') AS LogicalOp,
        n.value('@EstimateCPU', 'float') AS EstimateCPU,
        n.value('@EstimateIO', 'float') AS EstimateIO,
        n.value('@EstimatedTotalSubtreeCost', 'float') AS TotalSubtreeCost,
        CAST(NULL AS varchar(500)) AS ParentNodePath,
        n.query('.') AS NodeXml
    FROM @PlanXML.nodes('//RelOp') AS q(n)
    UNION ALL
    -- 递归提取嵌套的子RelOp节点
    SELECT
        CAST(xn.NodePath + '/RelOp[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS varchar(10)) + ']' AS varchar(500)),
        n.value('@PhysicalOp', 'varchar(100)'),
        n.value('@LogicalOp', 'varchar(100)'),
        n.value('@EstimateCPU', 'float'),
        n.value('@EstimateIO', 'float'),
        n.value('@EstimatedTotalSubtreeCost', 'float'),
        xn.NodePath,
        n.query('.')
    FROM XmlNodes xn
    CROSS APPLY xn.NodeXml.nodes('.//RelOp') AS q(n)
    WHERE NOT EXISTS (
        SELECT 1 FROM XmlNodes xn2 WHERE xn2.NodePath = CAST(xn.NodePath + '/RelOp[' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS varchar(10)) + ']' AS varchar(500))
    )
)
-- 计算成本占比（基于根节点总成本）
SELECT
    NodePath,
    ParentNodePath,
    PhysicalOp,
    LogicalOp,
    EstimateCPU,
    EstimateIO,
    TotalSubtreeCost,
    ROUND((TotalSubtreeCost / (SELECT TOP 1 TotalSubtreeCost FROM XmlNodes WHERE ParentNodePath IS NULL)) * 100, 2) AS CostPercentage
FROM XmlNodes
ORDER BY NodePath;

3. 脚本说明 #

• 递归CTEXmlNodes会遍历所有层级的RelOp节点，不管查询计划嵌套多深，都能完整提取所有执行步骤。
• 提取的字段包含节点的物理/逻辑操作类型、CPU/IO预估成本、子树总成本，以及节点路径和父节点路径，清晰展示执行计划的层级关系。
• CostPercentage字段计算当前节点成本占整个查询计划总成本的比例，和SSMS界面显示的占比一致。
• 核心的RelOp节点结构是SQL Server执行计划的标准结构，因此该脚本适用于绝大多数查询的执行计划解析。

4. 注意事项 #

• 如果执行计划XML过大，可适当调整varchar(500)的长度（比如改为varchar(1000)），避免节点路径被截断。
• 部分特殊节点（如根级QueryPlan）可能没有PhysicalOp属性，对应字段会显示NULL，属于正常情况。

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