The following schema for Microsoft SQL Server code name "Denali" describes
output from the showplan functionality in XML format.
Microsoft does not make any representation or warranty regarding the
schema or any product or item developed based on the schema. The schema
is provided to you on an AS IS basis. Microsoft disclaims all express,
implied and statutory warranties, including but not limited to the implied
warranties of merchantability, fitness for a particular purpose, and freedom
from infringement. Without limiting the generality of the foregoing,
Microsoft does not make any warranty of any kind that any item developed
based on the schema, or any portion of the schema, will not infringe any
copyright, patent, trade secret, or other intellectual property right of any
person or entity in any country. It is your responsibility to seek licenses
for such intellectual property rights where appropriate.
MICROSOFT SHALL NOT BE LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR
IN CONNECTION WITH THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY
DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS),
PUNITIVE OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF
SUCH DAMAGES.
(c) Microsoft Corporation. All rights reserved.
Last updated: 07/31/11
This is the root element
The statement block that contains many statements
the type that contains the basic statement information
The simple statement that may or may not contain query plan, UDF plan or Stored Procedure plan
Use database statement
Complex statement type that is constructed by a condition, a then clause and an optional else clause.
The cursor type that might have one or more cursor operations, used in DECLARE CURSOR, OPEN CURSOR and FETCH CURSOR
The cursor type that might have one or more cursor operations, used in DECLARE CURSOR, OPEN CURSOR and FETCH CURSOR
Shows the plan for the UDF or stored procedure
The number of occure time depends on how we define the cursor
schema. In shiloh, the OPEN CURSOR and FETCH CURSOR doesn't show any plan and won't raise
error if the cursor doesn't exist. So we must keep the same behaivor, so the minOccurs is 0. If we allow
the declare cursor to be executed in showplan mode, then the open cursor and declare cursor will have
plan in showplan mode, the minOccurs will be 1
Spill warning information
Query wait information
Warning information for plan-affecting type conversion
List of all possible iterator or query specific warnings (e.g. hash spilling, no join predicate)
For memory consuming relational operators, show fraction of memory grant iterator will use
Provide memory grant estimate as well as actual runtime memory grant information.
Serial required/desired memory attributes are estimated during query compile time for serial execution.
The rest of attributes provide estimates and counters for query execution time considering actual degree of parallelism.
Provide hardware-dependent properties that affect cost estimate (and hence, query plan choice), as seen by the Query Optimizer.
EstimatedAvailableMemoryGrant is an estimate of what amount of memory (KB) will be available for this query at the execution time to request a memory grant from.
EstimatedPagesCached is an estimate of how many pages of data will remain cached in the buffer pool if the query needs to read it again.
EstimatedAvailableDegreeOfParallelism is an estimate of number of CPUs that can be used to execute the query should the Query Optimizer pick a parallel plan.
Runtime information provided from statistics_xml for each relational iterator
Runtime partition information provided in statistics xml for each relational iterator that support partitioning
Additional information about an indexed view. It includes all tables in the query that were replaced by the indexed view.
Additional information about a rollup. The highest level is the number of group by columns.
A level that is output by the rollup. Level 0 is the base aggregation, equivalent to the statement without 'WITH ROLLUP'. The highest level is the grand total, or group by all. Level 0 is always output, and at least one higher level.
Additional information about Star Join structure.
Arbitrary content type
Information on parallel thread usage.
Branches: Attribute. total number of concurrent branches of query plan.
Query would need additional worker threads of at least (Branches)* (Degree of Parallelism)
UsedThreads: Attribute maximum number of used parallel threads. This is available only for statistics XML
Then follows a list of one or more ThreadReservation elements.
Information on how parallel threads are reserved on NUMA node
NodeId: ID of NUMA node where this query is chosen to run
ReservedThreads: number of reserved parallel thread on this NUMA node
New Runtime information:
DegreeOfParallelism
MemoryGrant (in kilobytes)
New compile time information:
mem fractions
CachedPlanSize (in kilobytes)
CompileTime (in milliseconds)
CompileCPU (in milliseconds)
CompileMemory (in kilobytes)
Parameter values used during query compilation
NonParallelPlanReason
Scalar expression. If root of scalar tree contains semantically equivalent string representation of entire expression
These are the logical operators to which "query"
portions of T-SQL statement are translated. Subsequent
to that translation, a physical operator is chosen for
evaluating each logical operator. The SQL Server query
optimizer uses a cost-based approach to decide which
physical operator will implement a logical operator.
Each of the physical operator is an iterator. An iterator
can answer three method calls: Init(), GetNext(), and Close().
Upon receiving an Init() call, an iterator initializes itself,
setting up any data structures if necessary. Upon receiving a
GetNext() call, the iterator produces the "next" packet of
data and gives it to the iterator that made the GetNext() call.
To produce the "next" packet of data, the iterator may have to
make zero or more GetNext() (or even Init()) calls to its
children. Upon receiving a Close() call, an iterator performs
some clean-up operations and shuts itself down. Typically, an
iterator receives one Init() call, followed by many GetNext()
calls, and then a single Close() call.
The "query" portion of a T-SQL statement is typically a tree
made up of iterators.
Usually, there is a one-to-many mapping among logical operators
and physical operators. That is, usually multiple physical operators
can implement a logical operator. In some cases in SQL Server,
however, a physical operator can implement multiple logical operators.
The set options that affects query cost