Viewing and Interpreting XML Showplans


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:


 



  1. Generic information such as logical and physical operator names, optimizer cost estimates, etc. as attributes.

  2. 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:


 


<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:


 



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

<QueryPlan DegreeOfParallelism=1 CachedPlanSize=24” …>


 



  1. 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>


                <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>


 



  1. The runtime information is shown per thread, and not aggregated as in legacy showplan.

  2. 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

Comments (1)

  1. The star join optimization technique is an index based optimization designed for data warehousing scenarios