I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server – Find the most expensive operations in Execution plans

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.

Print | posted on Thursday, January 21, 2010 12:14 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server – Find the most expensive operations in Execution plans

Hey Mladen

Nice! Simple but quiet useful output.

Thanks
Flo
1/21/2010 1:47 PM | Florian Reischl
Gravatar

# 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
1/21/2010 2:14 PM | Kevin Kline
Gravatar

# 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.
1/21/2010 2:19 PM | Mladen
Gravatar

# 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.
1/21/2010 4:48 PM | Grant Fritchey
Gravatar

# 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
1/21/2010 7:42 PM | Brad Schulz
Gravatar

# re: SQL Server – Find the most expensive operations in Execution plans

Brad that's great idea! thanx.
1/21/2010 8:09 PM | Mladen
Gravatar

# 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!
1/22/2010 10:15 PM | Brent Ozar
Gravatar

# 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.
1/22/2010 10:29 PM | Mladen
Gravatar

# 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
1/22/2010 11:28 PM | Brad Schulz
Gravatar

# re: SQL Server – Find the most expensive operations in Execution plans

@Brad
Nice...very NICE!!!
1/22/2010 11:41 PM | Mladen
Gravatar

# re: SQL Server – Find the most expensive operations in Execution plans

Priceless! Thanks for sharing....
1/25/2010 1:10 PM | argatxa
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET