Viewing and Interpreting XML Showplans

Article
10/06/2006

As mentioned in our previous blog posting, SQL Server 2005 supports Showplan generation in XML format. XML-based Showplans provide greater flexibility in viewing query plans and saving them to files as compared to legacy Showplans. In addition to the usability benefits, XML-based Showplans also contain certain plan-specific information which is not available in Legacy Showplans. For example, Showplan XML contains the cached plan size, memory fractions (how memory grant is to be distributed across operators in the query plan), parameter list with values used during optimization, and missing indexes information which is not available with the legacy Showplan All option. Similarly Statistics XML contains additional information such as degree of parallelism, runtime memory grant, parameter list with actual values for all parameters used, and execution statistics such as count of rows/executes aggregated per thread (in a parallel query) as compared to the legacy Statistics Profile option. Such information is very useful in analyzing query compilation, execution and performance issues, hence using the new XML features for Showplan is highly recommended.

Showplan XML can be generated in two ways:

1. Using T-SQL SET options

2. Using SQL Server Profiler trace events

With the SET options, Showplan XML returns batch-level information, i.e. it produces one XML document per T-SQL batch. If the T-SQL batch contains several statements, it generates one XML node per statement and concatenates them together. However when Showplan XML is generated using trace events, it only generates statement-level information. Let’s analyze the Showplan XML output for the following query (see attached document - showplan.xml):

use nwind

set showplan_xml on

SELECT ContactName, OrderDate

FROM Customers inner join Orders

ON Orders.CustomerID = Customers.CustomerID

WHERE Orders.ShipCountry = 'Canada'

set showplan_xml off

The root element of the document contains the Showplan XML namespace attribute with the location of the Showplan XML schema, and the SQL Server build information. It contains batch and statement sub-elements. Each statement contains a "StatementText" attribute that describes the T-SQL query being executed, a "StatementId" attribute indicating the relative position of the statement in the batch, the relative cost of the statement and the level of optimization used to generate the query plan output. It also contains additional information like SET options in effect when executing the query.

<Batch>

<StmtSimple StatementText="SELECT ContactName, OrderDate FROM Customers inner join Orders ..

...

The QueryPlan node contains plan information such as size of the compiled plan, compilation time, etc. which is useful for debugging purposes. The iterators in the query plan are represented as nested elements, each of type ‘RelOp’. Every ‘RelOp’ element contains two types of information:

Generic information such as logical and physical operator names, optimizer cost estimates, etc. as attributes.
Operator specific information such as a list of output columns, a set of defined values, predicates, database objects on which they operate (tables/indexes/views), etc. as sub-elements.

For example, the topmost ‘RelOp’ in our example is a ‘Nested Loops’ that contains the following generic information:

And the following operator specific information:

</OutputList>

</OuterReferences>

…

</NestedLoops>

The runtime counterpart of Showplan XML is called ‘Statistics XML’. It displays query execution “statistics” by executing the query and aggregating runtime information on a per-iterator basis. To obtain the Statistics XML output, use the following SET option.

set statistics xml on

Now lets analyze the Statistics XML output of the above query. The XML generated is semantically similar to the Showplan XML output, some of the key differences are:

In the QueryPlan node, an additional attribute indicating the degree of parallelism is present:

Each iterator ‘RelOp’ node contains an additional sub-element called RunTimeInformation that contains iterator execution profile such as the number of rows, number of executions, indexes accessed, join order, etc.

<RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="43" EstimateIO="0"

EstimateCPU="0.00017974" AvgRowSize="34" EstimatedTotalSubtreeCost="0.0308059" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">

</OutputList>

< RunTimeInformation >

< RunTimeCountersPerThreadThread = "0"ActualRows="43"ActualEndOfScans="1"ActualExecutions="1" />

</ RunTimeInformation >

The runtime information is shown per thread, and not aggregated as in legacy showplan.
For memory consuming iterators such sort or hash join, the memory grant information is printed.

In a later blog post, i will cover some of the Profiler Trace Events for Showplan.

Gargi Sur

SQL Server Query Processing Development Team

showplan.xml

Viewing and Interpreting XML Showplans

Additional resources