She can dig it!
D Sharon Pruitt

In the last couple plan cache posts, I talked about a couple items that are available within the SHOWPLAN XML for an execution plan.  These posts looked at how to find execution plans that utilized parallelism or contained information on missing indexes.  What wasn’t explained though is what is available within this XML schema.

SHOWPLAN XML The Schema

In this post, and subsequent posts, the information in the XML schema will be explored a little to show what is available and how to retrieve some of this information.  The aim here is to help equip you with what you need to go wherever you’d like in the plan cache.

To start with, queries that will be examining the plan cache will need to specify the XML schema for execution plans through a XMLNAMESPACE declaration.  This has been declare in the past couple posts, and the future posts, with the following statement:

WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

The URL above includes the location of the SHOWPLAN XML schema.  Browsing to the schema will provide insight into all of the possible elements and variables possible in an execution plan.

Start At The Top

To help guide you through the SHOWPLAN XML schema, let’s start at the top and look at the first few elements of the XML schema.  Before the elements, we’ll look at these elements in a snippet of an execution plan:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.4000.0">
<BatchSequence>
<Batch>
<Statements>
ALL OF THE INTERESTING STUFF
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

As you may have guessed, given the lack of detail, these initial layers of the SHOWPLAN XML don’t provide much information.  They do provide the entry into it and help define what’s to come.

Each of these elements are defined as:

• ShowPlanXML: The root element of the SHOWPLAN XML.  This element will appear only one time.  This Build attribute lets you know the version of SQL Server that the execution plan was generated on.
• BatchSequence: The child to the ShowPlanXML element.  This element provides the wrapper for one or more batches of T-SQL statements.  There can be only one BatchSequence element.  Like the police say, nothing to see here.
• Batch: The child of the BatchSequence element.  There will be 1 to n Batch elements.  In all of the plan caches that I’ve looked at, though, I’ve only seen a single Batch element in the plan cache.  There are often multiple Batch elements in estimated and actual plans.  This will happen when the T-SQL script has GO statements in it.
• Statements: The child of the Batch element.  This element provides a wrapper for   There can be 0 to n Statements elements.  I haven’t witnessed multiples of this element within a Batch element – not in the plan cache, estimated plan, and an actual plan.  Not even with Green Eggs and Ham.

Once the SHOWPLAN XML gets down to the Statements element, the meat and potatoes of the execution plan starts to come out.  Actually, beyond the SQL Server version, nearly all of the information in the execution plan will be children of this element.

Statements Element

Within Statements element there are five child elements that can exist.  These elements are StmtBlockTypes.  The different StmtBlockTypes are:

• StmtSimple: The simple statement that may or may not contain query plan, UDF plan or Stored Procedure plan.
• StmtCond: Complex statement type that is constructed by a condition, a then clause and an optional else clause.
• StmtCursor: The cursor type that might have one or more cursor operations, used in DECLARE CURSOR, OPEN CURSOR and FETCH CURSOR.
• StmtReceive: The cursor type that might have one or more cursor operations, used in DECLARE CURSOR, OPEN CURSOR and FETCH CURSOR.
• StmtUseDb: Use database statement

Each of these child elements will open up various items within the plan cache.  The next five posts will explore each of these and provide some methods for querying information from each one.

Wrap-Up

The overall purpose of this post was to help get into the plan cache and the SHOWPLAN XML.  This is the door to to the treasures deeper in the plan cache.  While the door isn’t necessarily interesting, opening it and knowing where to start looking is important.

Hopefully this has helped in that respect.  As I mentioned, the next few posts will actually have useful scripts within them.

Related posts:

1. Can You Dig It? – The Plan Cache
2. Can You Dig It? – Missing Indexes
3. Can You Dig It? – Parallelism in the Plan Cache