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);
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]