How to Analyze SQL Server Execution Plans from a Saved XML File
If you have an execution plan saved as an XML file, here's how you can read and analyze it in SQL Server.
📄 Step 1: Load the Execution Plan File
Use the OPENROWSET function to load your .xml file into a variable.
DECLARE @plancontents VARCHAR(MAX), @xml XML;
SET @plancontents = (
SELECT *
FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) AS FileData
);
🔍 Step 2: Retrieve Recent Queries Matching a Specific Pattern
You can extract the top 100 recent SQL queries that match a specific text pattern, along with their execution times and plan handles.
SELECT TOP 100
execquery.last_execution_time AS [Date Time],
execsql.TEXT AS [Script],
execquery.plan_handle
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
WHERE execsql.TEXT LIKE '%WITH Temp_Accounts (%'
ORDER BY execquery.last_execution_time DESC;
🧠 Step 3: Analyze Execution Plan Nodes
Once you have the execution plan XML, you can use XQuery to dig into specific attributes of each node, such as cost estimates and operation types.
;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,
c.query('.') AS ExecPlanNode
FROM @xml.nodes('//child::RelOp') AS T(c)
ORDER BY EstimatedTotalSubtreeCost DESC;
🔗 Useful References
- Escape XML Special Characters in SQL Server
- Working with XML and NULL Columns in SQL Server
- Querying XML Data Types in SQL Server
- Tracking CPU Utilization Trends in SQL Server
💡 Final Thoughts
By leveraging SQL Server's XQuery capabilities and DMVs, you can gain deep insights into query performance and execution behavior directly from saved plan files. This method is particularly useful when troubleshooting or optimizing complex queries.
