The following schema for Microsoft SQL Server 2012 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