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: 06/20/2022 This is the root element This is only found in the serialized xml for Gen3 external distributed statements. 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, Stored Procedure plan or dispatcher for parameter sensitive 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 occur 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 Exchange 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 UdfCpuTime: Cpu time of UDF in milliseconds UdfElapsedTime: Elapsed time of UDF 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) Spill Warning for last query plan stats 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. LastRequestedMemory: Memory in KB which was requested by the query from the memory manager during the last execution. IsMemoryGrantFeedbackAdjusted: Information regarding if the memory grant in this plan is adjusted based on memory grant feedback. 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) ExclusiveProfileTimeActive: true if the actual elapsed time (ActualElapsedms attribute) and the actual CPU time (ActualCPUms attribute) represent the time interval spent exclusively within the relational iterator. 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 This is the dispatcher expression in XML format for the parameter sensitive plan. This contains information related to the parameter sensitive predicate: Boundaries used to determine different ranges; Statistics information used to compute the boundaries; Predicate details. 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