from book SQL Server Execution Plans Third Edition

xml_plan_read.sql

WITH Top1Query
AS (SELECT TOP 1
           dest.text,
           deqp.query_plan
    FROM sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
        CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
    ORDER BY deqs.execution_count DESC)
SELECT TOP 3
       tq.text,
       op.value('@PhysicalOp', 'varchar(50)') AS PhysicalOp,
       RelOp.op.value('@EstimateCPU', 'float') + RelOp.op.value('@EstimateIO', 'float') AS EstimatedCost
FROM Top1Query AS tq
    CROSS APPLY tq.query_plan.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
    //RelOp') RelOp(op)
ORDER BY EstimatedCost DESC;

--Listing 13.5
WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT deqp.query_plan.value(N'(//MissingIndex/@Database)[1]', 'NVARCHAR(256)')
           AS DatabaseName,
       dest.text AS QueryText,
       deqs.total_elapsed_time,
       deqs.last_execution_time,
       deqs.execution_count,
       deqs.total_logical_writes,
       deqs.total_logical_reads,
       deqs.min_elapsed_time,
       deqs.max_elapsed_time,
       deqp.query_plan,
       deqp.query_plan.value(N'(//MissingIndex/@Table)[1]', 'NVARCHAR(256)')
           AS TableName,
       deqp.query_plan.value(N'(//MissingIndex/@Schema)[1]', 'NVARCHAR(256)')
           AS SchemaName,
       deqp.query_plan.value(N'(//MissingIndexGroup/@Impact)[1]', 'DECIMAL(6,4)')
           AS ProjectedImpact,
       ColumnGroup.value('./@Usage', 'NVARCHAR(256)') AS ColumnGroupUsage,
       ColumnGroupColumn.value('./@Name', 'NVARCHAR(256)') AS ColumnName
FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    CROSS APPLY deqp.query_plan.nodes('//MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup') AS t1(ColumnGroup)
    CROSS APPLY t1.ColumnGroup.nodes('./Column') AS t2(ColumnGroupColumn);