Execution plans! Don’t you just love them? They’re the first thing you look at when tuning a query or a stored procedure. But what do you do if you have a gigantic query play with 10’s of nodes? how do you find the most complex one? Where do you start?
What I’ve usually done in situations like that is to first find the node/statement with the highest cost and work from there. Now the highest cost can be IO, CPU, Row number or the good old SubTree cost which gives us a number based on all those counters. Let’s see how.
With SQL Server 2005 we got the option to show our execution plans in XML in two different ways: using the SET SHOWPLAN_XML ON option or choosing the Show Execution Plan XML option in the execution plan context menu.
These are the two queries we’ll work with:
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader SOH
SELECT *
FROM Sales.SalesOrderHeader SOH
join Sales.SalesOrderDetail SOD on SOH.SalesOrderID = SOD.SalesOrderID
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.2531.0">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="..." StatementId="1" StatementCompId="1" ... >
<StatementSetOptions ... />
<QueryPlan CachedPlanSize="8" CompileTime="1" CompileCPU="1" CompileMemory="152">
<RelOp ... > ... </RelOp>
</QueryPlan>
</StmtSimple>
<StmtSimple StatementText="..." StatementId="2" StatementCompId="2" ... >
<StatementSetOptions ... />
<QueryPlan CachedPlanSize="40" CompileTime="6" CompileCPU="6" CompileMemory="512">
<RelOp ... > ... </RelOp>
</Merge>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
First thing to do is to remove the xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan attribute part from the ShowPlanXML node. This is needed so that we don’t have problems with namespaces further on which simplifies the querying. You can leave the xmlns attribute but then you’ll have to use WITH XMLNAMESPACES to query it promperly. From this XML we can extract the costliest nodes and statements with the following code:
-- notice the removed
-- xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan
-- attribute
declare @xml xml = '
<ShowPlanXML Version="1.1" Build="10.0.2531.0">
... Shortened XML data...
</ShowPlanXML>'
-- go through all the execution plan nodes, get the attributes and sort on them
SELECT 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 returns just the node query for easier inspection
c.query('.') as ExecPlanNode
FROM -- this returns only nodes with the name RelOp even if they are children of children
@xml.nodes('//child::RelOp') T(c)
ORDER BY EstimatedTotalSubtreeCost DESC
-- go through all the SQL Statements, get the attributes and sort on them
SELECT c.value('.[1]/@StatementText', 'nvarchar(max)') as StatementText,
c.value('.[1]/@StatementSubTreeCost', 'nvarchar(max)') as StatementSubTreeCost,
c.value('.[1]/@StatementEstRows', 'nvarchar(max)') as StatementEstimateRows,
c.value('.[1]/@StatementOptmLevel', 'nvarchar(max)') as StatementOptimizationLevel,
-- this returns just the statement query for easier inspection
c.query('.') as ExecPlanNode
FROM -- this returns only nodes with the name StmtSimple
@xml.nodes('//child::StmtSimple') T(c)
ORDER BY StatementSubTreeCost DESC
In the results from the upper queries we can see that the ExecPlanNode is the XML of the processed node. By clicking on it we can inspect just that single node for further details.
We can see that the results are nicely sorted by cost and finding most expensive parts of your plan is easy. This may not look like a big deal but imagine having a stored procedure that generates 50 complex execution plans. Try going through that by hand and see how long you last.