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
go
set showplan_xml on
go
SELECT ContactName, OrderDate
FROM Customers inner join Orders
ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.ShipCountry = 'Canada'
go
set showplan_xml off
go
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.
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.0.9067.0">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="SELECT ContactName, OrderDate FROM Customers inner join Orders ..
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="24" CompileTime="1797" CompileCPU="1756" CompileMemory="328">
...
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:
For example, the topmost ‘RelOp’ in our example is a ‘Nested Loops’ that contains the following generic information:
<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">
And the following operator specific information:
<OutputList>
<ColumnReference Database="[nwind]" Schema="[dbo]" Table="[Customers]" Column="ContactName" />
<ColumnReference Database="[nwind]" Schema="[dbo]" Table="[Orders]" Column="OrderDate" />
</OutputList>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Database="[nwind]" Schema="[dbo]" Table="[Orders]" Column="CustomerID" />
</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
go
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:
<QueryPlan DegreeOfParallelism = "1" CachedPlanSize="24" …>
<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>
<ColumnReference Database="[nwind]" Schema="[dbo]" Table="[Customers]" Column="ContactName" />
<ColumnReference Database="[nwind]" Schema="[dbo]" Table="[Orders]" Column="OrderDate" />
</OutputList>
< RunTimeInformation >
< RunTimeCountersPerThread Thread = "0" ActualRows="43" ActualEndOfScans="1" ActualExecutions="1" />
</ RunTimeInformation >
In a later blog post, i will cover some of the Profiler Trace Events for Showplan.
- Gargi Sur
SQL Server Query Processing Development Team
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.