Summary: Learn how to load saved SQL execution plan (.xml) files into SQL Server, query historical plan handles, and use XQuery to extract performance metrics like CPU and IO costs.
How to Analyze SQL Server Execution Plans from a Saved XML File
If you have an execution plan saved as an XML file (often with a .sqlplan extension), you aren't limited to just viewing it graphically. You can load it into SQL Server and query the XML directly to find specific performance bottlenecks. Here is how to read and analyze it using T-SQL.
1. Load the Execution Plan File
The OPENROWSET function is the most efficient way to pull an external .xml file into a SQL variable for processing.
DECLARE @xml XML;
-- Load file content into XML variable
SELECT @xml = CAST(FileData.BulkColumn AS XML)
FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) AS FileData;
-- Verify the content
SELECT @xml AS LoadedPlan;
2. Retrieve Historical Queries via DMVs
If you don't have the file but need to find a plan for a recently executed query, use the Dynamic Management Views (DMVs). This snippet searches for a specific text pattern in your plan cache.
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;
3. Deep-Dive Analysis using XQuery
Once your plan is in an XML variable, use XQuery and namespaces to extract specific attributes like PhysicalOp, EstimateCPU, and EstimatedTotalSubtreeCost for every operator in the plan.
-- Define the namespace for SQL Server Showplans
;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]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
c.value('.[1]/@EstimatedTotalSubtreeCost', 'float') AS SubtreeCost,
c.value('.[1]/@EstimateRows', 'float') AS EstimatedRows,
c.value('.[1]/@EstimateCPU', 'float') AS EstimateCPU,
c.query('.') AS NodeXml
FROM @xml.nodes('//RelOp') AS T(c)
ORDER BY SubtreeCost DESC;
💡 Final Thoughts
By leveraging SQL Server's XQuery capabilities and DMVs, you can gain deep insights into query performance behavior directly from saved files. This method is essential for automated performance auditing or comparing plans across different server environments.
Found this optimization guide helpful? Share it with your SQL community or subscribe for more advanced performance tuning tips!
