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,
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
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
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):
set showplan_xml on
SELECT ContactName, OrderDate
FROM Customers inner join Orders
ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.ShipCountry = 'Canada'
set showplan_xml off
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.
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:
Generic information such as logical and physical operator names, optimizer cost estimates, etc. as attributes.
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
that contains the following generic information:
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
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:
In the QueryPlan node, an additional attribute indicating the degree of parallelism is present:
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.