How to read the SQL Server query plan file using T-SQL
In this post, I'll explain how to read an SQL Server execution query plan from the SSMS query file.
DECLARE @plancontents xml, @xml XML
SET @plancontents = (SELECT * FROM OPENROWSET(BULK 'P:\\Users\\hpatterson\\Documents\\myplan.sqlplan', SINGLE_CLOB) x)
-- Remove unnecessary namespace
-- SET @plancontents = REPLACE(@plancontents, 'xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"', '')
-- Store the cleaned content into the XML variable
SET @xml = CAST(@plancontents AS XML)
-- You can also get the query plan directly like this:
-- SELECT @xml = query_plan FROM sys.dm_exec_query_plan
SELECT @xml
-- Notice the removal of the xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan attribute
-- Now, let's process all the execution plan nodes and extract relevant details
WITH xmlnamespaces (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
c.value('.[1]/@PhysicalOp', 'nvarchar(max)') AS PhysicalOp,
c.value('.[1]/@LogicalOp', 'nvarchar(max)') AS LogicalOp,
c.value('.[1]/@AvgRowSize', 'nvarchar(max)') AS AvgRowSize,
c.value('.[1]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
c.value('.[1]/@Parallel', 'nvarchar(max)') AS Parallel,
c.value('.[1]/@EstimateRebinds', 'nvarchar(max)') AS EstimateRebinds,
c.value('.[1]/@EstimateRewinds', 'nvarchar(max)') AS EstimateRewinds,
c.value('.[1]/@NodeId', 'nvarchar(max)') AS NodeId,
c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') AS EstimatedTotalSubtreeCost,
c.value('.[1]/@EstimateRows', 'nvarchar(max)') AS EstimateRows,
c.value('.[1]/@EstimateIO', 'nvarchar(max)') AS EstimateIO,
c.value('.[1]/@EstimateCPU', 'nvarchar(max)') AS EstimateCPU,
-- This will return the node XML for easier inspection
c.query('.') AS ExecPlanNode
FROM
-- Only returns nodes with the name RelOp, including those that are nested
@xml.nodes('//child::RelOp') T(c)
ORDER BY EstimatedTotalSubtreeCost DESC
