Previously we talked about graphical execution plans (XML plans) in XML Plans Saga –Twilight. Continuing the saga, I will discuss the use of XML plans using T-SQL commands and how to decipher the information from the XML plans obtained.

Before I begin the New Moon episode… I do want to point out that reading XML can be a lot of fun and can lead to hair loss! So if you want to make sure that you don’t suffer from premature hair loss, then read on. Last time I discussed how the Management Studio UI can be used to examine graphical query plans and draw conclusions regarding your query performance and plan efficiency! This time around I shall fall back to using plain old T-SQL to generate XML plans and how to make sense out of them!

I will use the same query that I used in the Twilight post but this time, I shall use SET statements to generate the estimated XML plans.

set showplan_xml on
go
select top 100 chardata
from dbo.tblTechEd1
where id = 1
go
set showplan_xml off
go

The showplan_xml command generates the estimated query plan as an XML document in the Results tab. On clicking the XML document, you should be able to view the plan. This can also be done by right clicking on the graphical plan in Management Studio and selecting the “Show Execution Plan XML…” option.

Now that the mundane details are out of the way what are the important points to be noted for the massive XML document… <StmtSimple> under the <Statements> tag will have the information about query text.

<StmtSimple StatementText="select top 100 chardata from dbo.tblTechEd1 where id = 1 " StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="74.1451" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0x87B27FF4EF1A21CC" QueryPlanHash="0xD7DDE190FE13F388">

This will have a few important elements:

• StatementText – That is the query which you were executing
• StatementType – Denotes whether it is a SELECT, DML etc.
• StatementSubTreeCost – This is the estimated cost of the query
• StatementEstRows – The estimated number of rows that the query will return. This is to be taken with a pinch of salt at times when you have row goals and some specific factors involved which led to poor cardinality estimation. Further discussion in this area would cause me to digress from the actual topic of the post.
• StatementOptmLevel – This is the optimization level of the query. This can help you determine if the query had a trivial plan or underwent full optimization.
• *QueryHash and QueryPlanHash – You will see this in the XML plans for SQL Server 2008 and above. This was new feature that allowed you to pick out similar queries and query plans used by similar queries. Books Online has more information on how use these hash values.

<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

<QueryPlan CachedPlanSize="16" CompileTime="4" CompileCPU="4" CompileMemory="128">

The StatementSetOptions element in the XML plan will give you the set options used to execute the query. This can be very helpful when capturing query plans for troubleshooting query performance issues as certain SET option changes between two executions can affect query plans. Right below the set options element, you will find the QueryPlan element which will tell you the compile time, CPU and memory used for entire compilation. This is again useful for troubleshooting performance issues when you see a high number of waits for relatively long durations with the waittype as RESOURCE_SEMAPHORE_QUERY_COMPILE. I had written a post on how to extract the plan size, compile time, cpu and memory for the query plan using Showplan XML Query Compile profiler events. The memory value is in KB and the time and CPU usage is in milli-seconds.

<MissingIndexes>
              <MissingIndexGroup Impact="99.9546">
                <MissingIndex Database="[dbTechEd_1]" Schema="[dbo]" Table="[tblTechEd1]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[id]" ColumnId="1" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>

If the database engine flagged the query with a missing index recommendation, then you will see this just below the QueryPlan element which would be denoted by the MissingIndexes tag. This will give you all the information that you get from the missing indexes DMV query. If you want to pick up the missing indexes from your plan cache and generate the CREATE INDEX statements, then read my earlier post on the same endeavor.

The OutputList element will give you all the columns that will be returned by the query. This can be useful when T-SQL code has been written using best practices of writing all the queries with SELECT * (read: sarcasm!)

Now comes the information about the physical and logical operators in the query plan. In my previous post, I was demonstrating the various tidbits of information that you can pick up from the graphical plan for the Table Scan operator. So I shall stick to the Table Scan operator here as well and this will show you the columns being referenced along with the output list of columns, the id value that I used in the predicate and object on which the scan is being performed (see Screenshot 2)

This was all about the estimated execution plan. Now to move onto the actual execution plan which can be obtained by using SET STATISTICS XML ON command. Once you have the XML plan using the “Show Execution Plan XML…” option or by viewing the XML document displayed in the Results tab. Everything that I have mentioned above will apply to the execution plan as well along with a few additions since this is THE actual execution plan.

<QueryPlan DegreeOfParallelism="4" MemoryGrant="0" CachedPlanSize="16" CompileTime="4" CompileCPU="4" CompileMemory="128">

The first thing that you will notice is the QueryPlan element has two additional areas of interest. The DOP (DegreeOfParallelism) which tells you as the name says the degree of the parallelism used by the query. If the value is 1, then the query used a Serial Plan. If the value is greater than 1, then this was a parallel plan and the value will tell you the maximum number of parallel threads that were spawned by a physical operator in the plan. (Note that this is not the cumulative sum of all the parallel threads spawned during the execution of the query). Then there is the MemoryGrant value which is the amount (in KB) that was granted to the query. The details of all the memory grants on the SQL Server instance can be tracked using the DMV  sys.dm_exec_query_resource_semaphores.

The next difference that you will notice in the actual execution plan is the runtime information which will be enclosed with the RunTimeInformation tags. As you can see in Screenshot 4, you will notice that the RunTimeInformation has the actual number of rows and actual executions for each parallel thread. If it was a serial execution, then you will have the runtime statistics for Thread ID 0.

Now for some bonus information. The StmtSimple might have the following also for an execution plan:

StatementOptmEarlyAbortReason="GoodEnoughPlanFound"

This will tell you if optimization was aborted early and if yes, what was the reason. Sometimes, optimization can be aborted due to lack of available memory or due to a timeout. In this case, a good plan was found for the execution which is why optimization was aborted.

<ParameterList>
  <ColumnReference Column="@1" ParameterCompiledValue="(52)" ParameterRuntimeValue="(52)" />
</ParameterList>

The other nugget of information is the ParameterList node. This will give you the parameter value that was used to compile the plan (ParameterCompiledValue) and the actual value that was used during runtime (ParameterRuntimeValue). This is quite useful when analyzing stored procedure performance which use cached plans and for digging into parameter sniffing issues. The above snippet is picked from a different query and the SELECT statement that I used was:

StatementText="SELECT [session_id],[scheduler_id] FROM [sys].[dm_exec_requests] WHERE [session_id]=@1"

As you can see the above query, the StatementText value shows the @1 parameter. So you verify which was the parameter and also if the query was auto parameterized.

That is it for this part of the Saga, I shall discuss about fetching this information using SQL Server profiler traces in the next part “Eclipse”.