如何从执行计划XML中获取查询所用索引的列表？

需求：从SQL Server执行计划的XML中，提取出目标查询所使用的索引列表。

以下是尝试过的查询语句（已调整注释为中文）：

SELECT DISTINCT
    ExecutionPlan.value('(//@Index)[1]', 'NVARCHAR(MAX)') AS 所用索引
FROM @ExecutionPlan.nodes('//IndexSeek/Object') AS ExecutionPlans(ExecutionPlan)
UNION
-- 查找IndexScan操作中使用的索引
SELECT DISTINCT
    ExecutionPlan.value('(//@Index)[1]', 'NVARCHAR(MAX)') AS 所用索引
FROM @ExecutionPlan.nodes('//IndexScan/Object') AS ExecutionPlans(ExecutionPlan);

优化说明 #

原语句中(//@Index)[1]的写法存在问题，//会遍历整个XML文档查找第一个Index属性，而非当前Object节点对应的索引。正确写法应直接取当前节点的@Index属性，避免误取其他节点的索引值：

SELECT DISTINCT
    ExecutionPlan.value('@Index', 'NVARCHAR(MAX)') AS 所用索引
FROM @ExecutionPlan.nodes('//IndexSeek/Object') AS ExecutionPlans(ExecutionPlan)
UNION
SELECT DISTINCT
    ExecutionPlan.value('@Index', 'NVARCHAR(MAX)') AS 所用索引
FROM @ExecutionPlan.nodes('//IndexScan/Object') AS ExecutionPlans(ExecutionPlan);

如果需要包含索引所属的完整对象名，可扩展提取库、 schema 和表名：

SELECT DISTINCT
    ExecutionPlan.value('@Database', 'NVARCHAR(MAX)') + '.' +
    ExecutionPlan.value('@Schema', 'NVARCHAR(MAX)') + '.' +
    ExecutionPlan.value('@Table', 'NVARCHAR(MAX)') + '.' +
    ExecutionPlan.value('@Index', 'NVARCHAR(MAX)') AS 完整索引名
FROM @ExecutionPlan.nodes('//IndexSeek/Object | //IndexScan/Object') AS ExecutionPlans(ExecutionPlan);

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