Get list of used indexes in query from 'show execution plan xml'

Question

How can I get from execution plan xml list of used indexes by a query?

I've tried below query

SELECT DISTINCT
    ExecutionPlan.value('(//@Index)[1]', 'NVARCHAR(MAX)') AS UsedIndex
FROM @ExecutionPlan.nodes('//IndexSeek/Object') AS ExecutionPlans(ExecutionPlan)
UNION
-- Find indexes used in IndexScan operations
SELECT DISTINCT
    ExecutionPlan.value('(//@Index)[1]', 'NVARCHAR(MAX)') AS UsedIndex
FROM @ExecutionPlan.nodes('//IndexScan/Object') AS ExecutionPlans(ExecutionPlan);

Answer 1

A very simple solution would be as follows -

DECLARE @ExecutionPlan XML;

SELECT TOP 1
       @ExecutionPlan = y.query_plan
FROM sys.dm_exec_query_stats AS x
    CROSS APPLY sys.dm_exec_query_plan(x.plan_handle) AS y
    ORDER BY x.last_execution_time DESC;

WITH XMLNAMESPACES
(
    DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT DISTINCT
       RelOp.typ.value(N'@PhysicalOp', N'sysname') AS operation
     , Obj.typ.value(N'@Index', N'sysname') AS index_name
     , Obj.typ.value(N'@Table', N'sysname') AS table_name
FROM @ExecutionPlan.nodes(N'//RelOp') RelOp(typ)
    CROSS APPLY RelOp.typ.nodes(N'IndexScan/Object') Obj(typ)

This will provide the table name, index name, and the type of operation. an example below would be -

operation               index_name         table_name
----------------------- ------------------ ----------------
Clustered Index Seek    [PK_ProductID]     [Product]
Index Seek              [UQIDX_SN]         [ProductHistory]

Answer 2

XML Query plans have a namespace in them which you need to reference when you do your query. Try this:

;with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT DISTINCT
    ExecutionPlan.value('@Index', 'NVARCHAR(130)') AS UsedIndex
FROM @ExecutionPlan.nodes('//IndexScan/Object') AS ExecutionPlans(ExecutionPlan)
UNION
---- Find indexes used in IndexScan operations
SELECT DISTINCT
    ExecutionPlan.value('@Index', 'NVARCHAR(130)') AS UsedIndex
FROM @ExecutionPlan.nodes('//IndexSeek/Object') AS ExecutionPlans(ExecutionPlan);

Note, I'm assuming the XML namespace in your queryplan is the same as it is in mine. Maybe double check your XML and make sure the xmlns it declares matches the URL I provided