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.
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.
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:
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.
Within Statements element there are five child elements that can exist. These elements are StmtBlockTypes. The different StmtBlockTypes are:
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.
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.