Systems Engineering and RDBMS

XML Execution Plans in SQL Server

Posted by decipherinfosys on October 8, 2008

Prior to SQL Server 2005, one could only see text based execution plans and graphical execution plans for the queries. Graphical execution plans are of course much easier to read and understand as compared to the text based plans. In SQL Server 2005, MSFT introduced XML plans as well.

One question that we got from one of our readers was that since text based execution plans were difficult to analyze, why did MSFT introduce XML based plans? There are a couple of reasons that come to mind: First and foremost, XML is a common file format which can be used programmatically, it can be exported out from one system to another and makes sharing of the plan much more easier and they also provide a lot more information than the text based plans.

There is also undocumented data stored in the XML plans like parameter lists with values that are used during optimization, missing indexes information, memory fractions and cached plan size. XML plans can also be used to force plans using plan guides. So, how do you go about getting the XML plan? In the profiler, you can get it easily by selecting the right events and columns and you can also save the deadlock events as XML as we had shown in one of our posts before.

If you want to see the XML plan in SSMS, all you need to do is use the SET option to set it up. We can see the estimated XML plan or the actual XML plan. Let’s look into the Estimated XML plan first:

SET SHOWPLAN_XML ON;
GO
select count(*) as cnt, organization_id, title from dbo.contact group by organization_id, title order by cnt desc
GO

You will see the output in the form of an XML. You can click on this and it will open up in another window:

You can easily collapse or expand the elements using the – or + signs on the left side. If you want to review the common elements and attributes, the full schema is available at:

http://schemas.microsoft.com/sqlserver/2004/07/showplan

Besides the normal information that you would be familiar with if you have dealt with execution plans a bit, you will see attributes of the QueryPlan:

<QueryPlan CachedPlanSize=”22″ CompileTime=”2″ CompileCPU=”2″ CompileMemory=”224″>

The attributes are pretty much self explanatory – shows the plan size in the cache, the compile time, CPU cycles and the compile time memory used. Under the RelOP nodes, you will see the different physical operations that the optimizer is going to perform:

<RelOp NodeId=”0″ PhysicalOp=”Sort” LogicalOp=”Sort” EstimateRows=”2969″ EstimateIO=”0.0112613″ EstimateCPU=”0.0535295″ AvgRowSize=”20″ EstimatedTotalSubtreeCost=”0.212729″ Parallel=”0″ EstimateRebinds=”0″ EstimateRewinds=”0″>

NodeId = 0 represents the sort operation that it did and besides the estimated IO, CPU, sub-tree cost (all of which you can see from the graphical execution plan when you hover your mouse over it), you will also see EstimateRebinds and EstimateRewinds which can give us a much more accurate understanding of what happened in the query execution like how many times was a particular operator executed. Next, you can take a look at the OutputList element:

<OutputList>
<ColumnReference Database=”[SVIPTEMP]” Schema=”[dbo]” Table=”[contact]” Column=”TITLE” />
<ColumnReference Database=”[SVIPTEMP]” Schema=”[dbo]” Table=”[contact]” Column=”ORGANIZATION_ID” />
<ColumnReference Column=”Expr1003″ />
</OutputList>

Each column reference contains a set of attributes that describes that column.

The Actual XML plan is obtained by using: SET STATISTICS XML ON;

When you execute the same query, you will now get the data records as well as the plan. When you click on the plan, it will open up in a new window and the main difference between the actual plan and the estimated one is that the QueryPlan will now have some additional information – namely the DegreeOfParallelism and the amount of memory that was needed to run the query (MemoryGrant):

<QueryPlan DegreeOfParallelism=”0″ MemoryGrant=”168″ CachedPlanSize=”23″ CompileTime=”2″ CompileCPU=”2″ CompileMemory=”224″>

In addition, as can be expected, the actual plan also includes an element called RunTimeInformation:

<RunTimeInformation>
<RunTimeCountersPerThread Thread=”0″ ActualRows=”2690″ ActualRebinds=”1″ ActualRewinds=”0″ ActualEndOfScans=”1″ ActualExecutions=”1″ />
</RunTimeInformation>

It shows the thread, the actual rows, the actual rebinds and rewinds.

The main benefit that we see in the XML plan is the portability of the plan from one environment to another which makes it easier to troubleshoot issues from the production environment. That coupled with the deadlock XML output that we had given the link to above makes it very useful. For normal query tuning purposes, we still use and recommend the graphical option since it is much easier to read and also has a wealth of information.

2 Responses to “XML Execution Plans in SQL Server”

  1. scarydba said

    One other thing you can do with the XML plans that you just can’t with the graphical plans is run queries against them:
    SELECT RelOp.op.value(‘@NodeId’, ‘int’) AS NodeId,
    RelOp.op.value(‘@PhysicalOp’, ‘NVARCHAR(75)’) AS Operation,
    RelOp.op.value(‘@EstimateCPU’, ‘float’) AS EstimatedCPU,
    RelOp.op.value(‘@EstimateIO’, ‘float’) AS EstimatedIO
    FROM @xml.nodes(‘declare default element namespace
    “http://schemas.microsoft.com/sqlserver/2004/07/showplan”;
    //RelOp’) AS RelOp ( op )
    ORDER BY [EstimatedCPU] DESC

    Stuff like this works great against overly large execution plans that can be a pain in the bottom when you’re working with graphical plans.
    From: http://www.sql-server-performance.com/articles/per/Breaking_Down_Complex_Execution_Plans_p1.aspx

  2. Excellent point.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: