CODEWITHSUNDEEP
sql-serverxmlextractstructuresql-execution-plan
Elim
Elim

Reputation: 1

Extract execution plan in XML format to table

I'm trying to extract the data from SQL server execution plans in a generic way.

As an example the execution plan for

SELECT *
FROM sys.all_objects o1

as shown in SSMS is below

enter image description here

The UI shows nodes along with costs for each node and percentages. How can I extract this from the underlying XML into a table structure?

I've tried to query the XML by my self, but it seems that the XML structure is changing from query to query.

Upvotes: 0

Views: 1174

Answers (1)

Martin Smith
Martin Smith

Reputation: 454020

This should get you started (DB Fiddle example).

DECLARE @X XML = N'<?xml version="1.0" encoding="utf-16"?><ShowPlanXML ...';
        
DECLARE @Nodes TABLE
  (
     PlanId                    INT,
     NodeId                    INT,
     PhysicalOp                VARCHAR(200),
     EstimatedTotalSubtreeCost FLOAT,
     EstimatedOperatorCost     FLOAT,
     ParentNodeId              INT NULL,
     PRIMARY KEY(PlanId, NodeId)
  ); 
 
WITH  XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
plans AS
(
SELECT ROW_NUMBER() over (order by qp) as PlanId, qp.query('.') as plan_xml
FROM @X.nodes('//QueryPlan') n(qp)
)
INSERT @Nodes(PlanId, NodeId, PhysicalOp, EstimatedTotalSubtreeCost, ParentNodeId) 
SELECT PlanId, 
       NodeId = relop.value('@NodeId', 'int'), 
       PhysicalOp = relop.value('@PhysicalOp', 'varchar(200)'), 
       EstimatedTotalSubtreeCost = relop.value('@EstimatedTotalSubtreeCost', 'float'),
       /*XPath ancestor axis not supported so just go up a few levels and look for the closest ancestor Relop*/
       ParentNodeId = COALESCE(
       relop.value('..[local-name() = "RelOp"]/@NodeId', 'int'),
       relop.value('../..[local-name() = "RelOp"]/@NodeId', 'int'),
       relop.value('../../..[local-name() = "RelOp"]/@NodeId', 'int'),
       relop.value('../../../..[local-name() = "RelOp"]/@NodeId', 'int')
       )
FROM plans
CROSS APPLY plan_xml.nodes('//RelOp') n(relop);

UPDATE N1
SET EstimatedOperatorCost = EstimatedTotalSubtreeCost -  ISNULL((SELECT SUM(EstimatedTotalSubtreeCost) FROM @Nodes N2 WHERE N1.PlanId = N2.PlanId AND N2.ParentNodeId = N1.NodeId),0)
FROM @Nodes N1


SELECT *,
       EstPctOperatorCost = FORMAT(EstimatedOperatorCost/MAX(EstimatedTotalSubtreeCost) OVER (PARTITION BY PlanId), 'P0')
FROM @Nodes

The execution plan is a tree - there are likely more elegant ways of getting the parent operator than my attempt!

The above is not battle tested across a sample size of more than two execution plans so you may well encounter issues with it that you will need to fix.

You can visit the URI http://schemas.microsoft.com/sqlserver/2004/07/showplan to see information about the various schemas though for some reason I've never got to the bottom of it displays "The request is blocked." for me unless I use incognito mode.

Upvotes: 2

Related Questions