Common properties
Introduction
To really understand what each operator’s role within an execution plan is, it is not enough to just look at the graphical plan to check all operators. You need to dig a level deeper, to the properties of each operator.
Property list
This table below lists the properties that you can find on all, or at least most operators; and that have a common meaning wherever you find them. There are also many properties that are specific to a single operator or a small group of operators; and a few properties that have the same name but a different meaning depending on the operator. Those will not be described here, but on the operator’s own page.
| Property name | Description |
|---|
Property name | Description |
|---|---|
| Actual Execution Mode | Did the operator run in row mode or in batch mode? Only available in execution plan plus run-time statistics. Introduced in SQL Server 2012. |
| Actual I/O Statistics | Shows the number of pages read by the operator during its operation, broken down into logical reads, physical reads, read-ahead reads; LOB logical reads, LOB physical reads, LOB read-ahead reads (for large object data); and number of scans. Only present on operators that have the ability to do I/O (such as seek, scan, and lookup operators, but also on spools or on operators that have a potential to spill to tempdb). When reading data from columnstore indexes, either in a Columnstore Index Scan or in a Key Lookup that targets a columnstore index, only the number of scans is counted; all other counters are always shown as zero. When reading data from memory-optimized indexes, this property is not included. Only available in execution plan plus run-time statistics. Introduced in SQL Server 2014. |
| Actual Number of Batches | The number of batches the operator returned. Shows the total number of batches over all executions. Zero when running in row mode. Only available in execution plan plus run-time statistics. Introduced in SQL Server 2012. |
| Actual Number of Rows | The number of rows the operator returned. Shows the total number of rows over all executions. In Management Studio 18.5 and higher, this is displayed as Actual Number of Rows for All Executions. Only available in execution plan plus run-time statistics and live execution plan. |
| Actual Number of Rows for All Executions | The number of rows the operator returned. Shows the total number of rows over all executions. The actual name of this property in the underlying execution plan XML is Actual Number of Rows, but Management Studio 18.5 and higher label it as Actual Number of Rows for All Executions to help prevent confusion. Only available in execution plan plus run-time statistics and live execution plan. |
| Actual Rebinds | The number of times the operator was initialized as a rebind. Is displayed on all operators, but only counted on operators that support rebinding and rewinding. Since the first execution of an operator can be considered functionally equivalent to a rebind, it is included in the Actual Rebinds counter. This is not consistent with the Estimated Rebinds property, where the first execution is not included. Only available in execution plan plus run-time statistics. |
| Actual Rewinds | The number of times the operator was initialized as a rewind. Is displayed on all operators, but only counted on operators that support rebinding and rewinding. Only available in execution plan plus run-time statistics. |
| Actual Time Statistics | This property lists information about CPU time (time spent on the processor, excluding waits) and elapsed time (total processing time including waits) for the operator. Unfortunately, the way this metric is computed and displayed is (at least at this time) inconsistent between batch mode and row mode plans. In row mode parallel plans, additional confusing inconsistencies can occur. See here for detailed information.
Introduced in SQL Server 2014. |
| CloseTime | In a live execution plan (aka live query statistics), this property shows the time the operator received its Close() call, represented as the number of microseconds since the server (not the service!) started. If the operator is still running, this property is 0. Only available in live execution plan. |
| CompletionEstimate | In a live execution plan (aka live query statistics), this property shows the ratio of Actual Number of Rows for All Executions divided by Estimated Number of Rows for All Executions. When the Actual Number of Rows for All Executions exceeds the Estimated Number of Rows for All Executions, this property is capped at 0.99 while the operator is still running. When the operator is finished, this property is always set to 1. Only available in live execution plan. |
| Defined Values | Definitions for each of the columns that is computed within the operator and added to the output stream. See Compute Scalar for a list of non-standard functions that can be used in the Defined Values expressions. |
| Description | A short description of what the operator does. |
| Estimated CPU Cost | An estimation of how much the CPU usage of the operator will contribute to the total plan cost, per execution of the operator. Determined during query optimization. |
| Estimated Data Size | An estimation of the expected size (in bytes) of all rows to be returned by the operator. Determined during query optimization. This property is not actually included in the execution plan. Tools such as Management Studio compute it by multiplying Estimated Number of Rows and Estimated Row Size. Management Studio only exposes this computed number only as a property of the data stream (on the arrows), and not as a property of the operators (on the icons). |
| Estimated Execution Mode | The requested execution mode (row mode or batch mode). Determined during query optimization. Introduced in SQL Server 2012. |
| Estimated I/O Cost | An estimation of how much the I/O required by the operator will contribute to the total plan cost, per execution of the operator. Determined during query optimization. |
| Estimated Number of Executions | An estimation of how often the operator will be initialized during execution of the plan. Always 1 except for operators in the inner input of a Nested Loops operator. This property is not actually included in the execution plan. Tools such as Management Studio compute it by adding Estimated Rebinds and Estimated Rewinds, plus 1 for the first execution. |
| Estimated Number of Rows | An estimation of how many rows the operator will return per execution. Has to be multiplied to Estimated Number of Executions to get the estimated total number of rows (which can then be compared to the Actual Number of Rows). Determined during query optimization. Renamed in Management Studio 18.5 to Estimated Number of Rows Per Execution. |
| Estimated Number of Rows for All Executions | An estimation of how many rows the operator will return for all (estimated) executions combined. This property is not actually included in the execution plan. Tools such as Management Studio (version 18.5 and higher) computed it by multiplying the Estimated Number of Rows to the Estimated Number of Executions. |
| Estimated Number of Rows Per Execution | An estimation of how many rows the operator will return per execution. Has to be multiplied to Estimated Number of Executions to get the estimated total number of rows (which can then be compared to the Actual Number of Rows for All Executions). Determined during query optimization. The actual name of this property in the underlying execution plan XML is Estimated Number of Rows, but Management Studio 18.5 and higher label it as Estimated Number of Rows Per Execution to help prevent confusion. |
| Estimated Operator Cost | An estimation of the total cost of the operator, usually equal to the sum of Estimated CPU Cost and Estimated I/O Cost, but for all executions. Note that this property is not actually included in the execution plan. Tools such as Management Studio compute it by subtracting the Estimated Subtree Cost of the direct descendants of the operator from the Estimated Subtree Cost of the operator itself. Management Studio presents this not only as a number, but also as a percentage that is computed as the ration of this number against the Estimated Subtree Cost of the root node of the execution plan. |
| Estimated Rebinds | An estimation of how often the operator will be initialized as a rebind during execution of the plan. Is displayed on all operators, but only counted on operators that support rebinding and rewinding. Determined during query optimization. Even though the first execution of an operator can be considered functionally equivalent to a rebind, it is excluded from the Estimated Rebinds property. This is not consistent with the Actual Rebinds property, where the first execution is not excluded. |
| Estimated Rewinds | An estimation of how often the operator will be initialized as a rewind during execution of the plan. Is displayed on all operators, but only counted on operators that support rebinding and rewinding. Determined during query optimization. |
| Estimated Row Size | An estimation of the average size (in bytes) of the rows returned by the operator. This will be an exact value if all columns returned are fixed-length. Determined during query optimization. |
| Estimated Subtree Cost | An estimation of the total cost incurred by this operator and all its child nodes combined, for all executions combined. This value is based on the Estimated CPU Cost and Estimated IO Cost of this operator and its child operators (multiplied by Estimated Number of Executions where applicable), but adjusted as needed based on undocumented extra rules and formulas. For the top-left operator of any plan, this is the estimated cost of the total plan, which was used during query optimization to identify the “cheapest” plan. Determined during query optimization. |
| EstimateRowsWithoutRowGoal | If a row goal was evaluated and used for a section of the execution plan, then this property is set on all operators in that section to show what the Estimated Number of Rows would have been without the row goal. Introduced in SQL Server 2016, Service Pack 2. |
| Internal Debugging Information | This property exposes additional internal information, intended for debugging by Microsoft. It is only exposed in execution plans when trace flag 8666 is active. Since this is an undocumented trace flag, it should never be used in production code, unless specifically requested by Microsoft support staff. The Internal Debugging Information typically contains one or more sub-properties. Depending on the operator, these can have various names and formats. Many of them are not correctly represented in Management Studio, so you will have to access the XML representation to see the actual data in this property. One sub-property that seems to be used on all operators is InternalBatchInfo. Its meaning is unknown at this time. In all my observations so far, it has the value ”00000000”. |
| Logical Operation | The logical operation that the operator is required to carry out when the plan is executed. This property is displayed on all operators, but only relevant for operators that support multiple logical operations. For operators that support a single logical operation only, the Logical Operation property is sometimes equal to the name of the operator, sometimes equal to a logical operation that can be implemented by more than one physical operator. For operators that support more than one logical operation, all possible values and their meaning are described on the page of that operator. |
| Memory Fractions | For any operator with a special memory requirement, this set of properties shows the fraction of the total available memory the operator can use, based on the optimizers understanding of which other operators are using memory at the same time.
|
| Memory Usage | For most operators with special memory requirements, this set of properties shows the actual memory usage (in KB) of the operator during execution. This is broken down in three subcategories, and within each subcategory further broken down by thread. Not all details of this property are known at this time; the description below of the three properties is currently based on assumptions.
Introduced in SQL Server 2016. |
| Node ID | A unique value (within the plan) to identify the node. |
| Number of Executions | An number of times the operator was initialized during execution of the plan. For operators that are not in the inner input of a Nested Loops operator, this will be equal to the degree of parallelism used when the plan executed. Only available in execution plan plus run-time statistics and live execution plan. |
| OpenTime | In a live execution plan (aka live query statistics), this property shows the time the operator received its first Open() call, represented as the number of microseconds since the server (not the service!) started. If the operator has not yet started, this property is 0. Only available in live execution plan. |
| Output List | A list of all the columns in the rows returned by the operator. By definition, the Output List of each operator is the input for the operator that calls it. All columns in the Output List either originate from reading a table or index (represented as database-, schema-, table-, and columnname) or are computed in this or another operator (represented as a short and usually mnemonic character string and a four digit number that is unique in the plan). |
| Parallel | True if the operator is executed in a parallel segment of the plan. Note that most operators do not change their behavior when running in parallel, they just do their own work on the subset of rows presented to them. |
| Physical Operation | The name of the operator. |
| StarJoinInfo | This property is added to all operators that were introduced by the optimizer as part of a star join optimization. It contains the following components:
|
| Status | In a live execution plan (aka live query statistics), this property shows the current status of the operator. Currently known values are:
|
Showing 1 to 38 of 38 entries
Implicit properties
Apart from the properties that are exposed in the execution plans, operators also have properties that are important to know, but not exposed in the execution plans. These are not exposed because they are part of how the operator always works, so apparently Microsoft felt no need to expose them, trusting that people who look at execution plans will know this. Below is a description of each of these properties. On the individual pages for all operators covered at this site, you will find the values for these implicit properties for the operator.
Note that the names of these properties are commonly used, but not official. You will not find them in any execution plan, but you may find them on many other internet sites.
| Property name | Description |
|---|
Property name | Description |
|---|---|
| Batch Mode support | In SQL Server 2012, batch mode execution was introduced. The first version only had batch mode support for a limited set of operators. Later versions added batch mode support for more operators. In current versions an operator can be batch mode enabled (meaning that it can be used both in row mode and in batch mode sections of execution plans) or row mode only. A very small group of operators exists that support batch mode only. |
| Blocking | Most operators are non-blocking, also called streaming or flowing. This means that the operator fetches the least amount of rows it can from its child operators (or from the storage subsystem) to produce a row, which it returns to its parent as soon as possible. Queries with an execution plan that uses only non-blocking operators will typically start producing output immediately, and continue to produce output until the query finishes. Operators that are blocking fetch all rows from their child operators before even returning a single row. One example of this is the Sort operator. It has to read its entire input before it returns even a single row, because the last row in might be the first one out. These operators typically do all the calls to their child operators, and all logic, in the Init() method; the GetNext() method then simply returns the next row from the now available results. Queries with an execution plans with blocking operators (depending on where in the plan and the rest of the operators) typically seem to do nothing for a long time, and then suddenly burst all their results to the client. There are also operators that are semi-blocking. This means that they don’t fetch all input before producing any output, but they do fetch a substantial amount. The exact details of this are explained per individual operator. |
| Memory requirement | Most operators have no special memory requirement. They need a small amount of working memory to keep the current row and status information, but nothing more. The operators that do need more memory typically need large amounts of memory. The total memory required by all of these is shown in the Memory Grant property of the plan as a whole, but the individual memory requirement per operator is not exposed in the execution plan. |
| Order guarantees | Operators can either impose an order (e.g. a Sort operator, or an Index Scan with the Ordered property set to True); preserve order; or not preserve order. An operator is only considered to be order-preserving when there is a 100% guarantee that rows are fetched, processed, and returned without changing order. When a query has an ORDER BY clause, or when an operator requires its input to be sorted, the optimizer tries to avoid a sort (by using other operators that impose order) or to sort data before joining to other tables (to minimize the size of the data to be sorted). This is only possible when the operators that the data flows through are order-preserving. |
| Parallelism aware | Most operators work the same whether an execution plan executes serially or in parallel. They do the same work they always do. The fact that they receive only a part of the total data while other copies of the operator are handling the other rows on another node is irrelevant to them. Some operators, though, do “know” when they are in a parallel plan and will change their behavior accordingly. These operators are called “parallelism aware”. Details of how parallelism affects these operators are on the pages of those operators. |
| Segment aware | The Segment operator marks the beginning of individual segments in a stream of data, by adding a special segment column. Some other operators may change their behavior when their input data contains such a segment column. These operators are called “segment aware”. Details of how these operators respond to segmented input data are on the pages of those operators. |
Showing 1 to 6 of 6 entries