The following schema for Microsoft SQL Server 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: 03/27/2017
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
Sort spill details
Hash spill details
Query wait information
Wait statistics during one query execution.
WaitType: Name of the wait
WaitTimeMs: Wait time in milliseconds
WaitCount: Number of waits
A list of query wait statistics.
Shows time statistics for single query execution.
CpuTime: CPU time in milliseconds
ElapsedTime: elapsed time in milliseconds
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.
SerialRequiredMemory: Required memory in KB if the query runs in serial mode. The query will not start without this memory.
SerialDesiredMemory: Memory estimated to fit intermediate results in KB if the query runs in serial mode.
RequiredMemory: Required memory in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialRequiredMemory.
DesiredMemory: Memory estimated to fit intermediate results in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialDesiredMemory.
RequestedMemory: Memory in KB which the query requests the memory manager to grant. This can be smaller than sum of RequiredMemory and DesiredMemory if it exceeds the maximum allowed for single query.
GrantWaitTime: Time in seconds if the query has to wait for successful memory grant.
MaxUsedMemory: Maximum memory in KB used by the query.
MaxQueryMemory: Maximum memory in KB allowed for single query.
Provide warning information for memory grant.
GrantWarningKind: Warning kind
RequestedMemory: Initial grant request in KB
GrantedMemory: Granted memory in KB
MaxUsedMemory: Maximum used memory grant in KB
Describe a trace flag used in SQL engine.
Collection of trace flags used in SQL engine.
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.
MaxCompileMemory is the maximum memory in KB allowed for query optimizer to use during compilation.
Information on single statistics used during query optimization.
Database : name of the database
Schema : name of the schema
Table : name of the table
Statistics : name of the statistics
ModificationCount : number of modifications since the last update
SamplingPercent : statistics sampling percentage
LastUpdate : date when the statistics was updated
List of statistics info used during query optimization
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
EffectiveDegreeOfParallelism: Max parallelism used by columnstore index build
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
The Adaptive Join element replaces a adaptive concat with Hash Join and Nested loops as inputs. This element
will have 3 inputs the two children of the HJ and the inner child of the NLJ. We append the required HJ and NLJ properties to the new
AdaptiveJoin showplan element.
Typical user defined table valued function doesn't have a relational child element. If a relational child
is present then the operator is a special internal table valued function that hosts native code.
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