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.

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 properly. 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 -- attributedeclare @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 themSELECT 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 xml 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)ORDERBY EstimatedTotalSubtreeCost DESC

-- go through all the SQL Statements, get the attributes and sort on themSELECT 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 xml for easier inspection c.query('.') as ExecPlanNodeFROM-- this returns only nodes with the name StmtSimple @xml.nodes('//child::StmtSimple') T(c)ORDERBY 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.

Sorting by EstimatedTotalSubTreeCost may be helpful when you're analyzing the XML for multiple statements, but for a single statement (like the query that JOINs the SalesOrderHeader and SalesOrderDetail for example), sorting by EstimatedTotalSubTreeCost (descending) will just put the very last operation (in this case, the Merge Join operator) at the top... Because that SubTreeCost is a grand total of all sub-operators (i.e. subtrees) that fed into it.

For individual statements, it might be more helpful if the results were sorted by EstimateIO+EstimateCPU (descending)... that way the most expensive (individual) operator will show at the top (in this case, the Clustered Index Scan of SalesOrderDetail).

Instead of having to worry about the double-quote stuff (and all the other steps)... how about the following to take care of everything...

1) When you have the ShowPlan XML up, instead of copying/pasting it into code, just do File -> Save As... and save it as an XML file... let's say we save it as C:\TEMP\MyPlan.XML.2) Run the following bit of code to populate the @xml variable: