Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

SQL Server – Find the most expensive operations in Execution plans

by Mladen Prajdić in  

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
 
They generate pretty simple plans that are perfect for our demo.
 
This is a heavily trimmed part of the upper queries execution plan XML:
 
<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 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 
-- 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 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)
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 xml 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.

PlanOperatorCostResults

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.

Legacy Comments
Florian Reischl
2010-01-21		 re: SQL Server – Find the most expensive operations in Execution plans
Hey Mladen

Nice! Simple but quiet useful output.

Thanks
Flo
Kevin Kline
2010-01-21		 re: SQL Server – Find the most expensive operations in Execution plans
Hi Mladen,

Excellent article and very useful code too! I'm not a heavy user of the XML SHOWPLAN, usually opting for the text formats.

Don't the SHOWPLAN TEXT and SHOWPLAN ALL variants already provide similar information? What would be the use case for wanting to use the XML variant? Is it easier to automate?

Thanks,

-Kev
Twitter @KEKline
Mladen
2010-01-21		 re: SQL Server – Find the most expensive operations in Execution plans
yes, they do.
however i've found that XML is the easiest to query and get stuff like this out of it.
if you have several huge plans this becomes very useful.

i use this to find the highest costs then go to visual plan for detailed inspection.
i still fidn it much easier to visualy read graphical plans than text or xml.
Grant Fritchey
2010-01-21		 re: SQL Server – Find the most expensive operations in Execution plans
Good one. I've been trying to push people to use the XML data for perusing execution plans for a while. It really does help.
Brad Schulz
2010-01-21		 re: SQL Server – Find the most expensive operations in Execution plans
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).

--Brad
Mladen
2010-01-21		 re: SQL Server – Find the most expensive operations in Execution plans
Brad that's great idea! thanx.
Brent Ozar
2010-01-22		 re: SQL Server – Find the most expensive operations in Execution plans
Great idea, and I'm running into two problems.

First, if your query has single quotes in it, that'll screw up SQL Server's XML processing. I opened the XML in Notepad++ and did a search/replace to strip out all of the single quotes.

Second, if you've got a really big query, you run into this error:

Msg 6335, Level 16, State 101, Line 4
XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.

I find that hilarious, because that's exactly the kind of query I'm trying to analyze - really big queries - and that's what SQL's XML can't handle! Doh!
Mladen
2010-01-22		 re: SQL Server – Find the most expensive operations in Execution plans
@Brent:
you're right i had to replace single ' to double ' but completely forgot to mention it. i'll add it next week.

the only thing i can suggest is that you split the plan into two xml variables. it shouldn't be a problem for the query.
Brad Schulz
2010-01-22		 re: SQL Server – Find the most expensive operations in Execution plans
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:

--Read the shoplan XML stuff into a VARCHAR variable:
DECLARE @plancontents VARCHAR(MAX)
SET @plancontents=(SELECT * FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) x)
--Get rid of the namespace stuff
SET @plancontents=REPLACE(@plancontents,'xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"','')
--Now put that into our XML variable
DECLARE @xml XML
SET @xml=@plancontents

Now you can run the rest of Mladen's code to shred the information in that @xml variable.

This will eliminate having to do the copy/paste, eliminate the worry about double-quoting, and eliminate the namespace stuff.

--Brad
Mladen
2010-01-22		 re: SQL Server – Find the most expensive operations in Execution plans
@Brad
Nice...very NICE!!!
argatxa
2010-01-25		 re: SQL Server – Find the most expensive operations in Execution plans
Priceless! Thanks for sharing....