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.

The list below is valid as of SQL Server 2017, CU12. It shows properties exposed in the graphical interface of SSMS version 17.9. I don’t check for properties that are visible in the execution plan XML only, but I do add these if I know about them.

Property nameDescription
Actual Execution ModeDid the operator run in row mode or in batch mode? Actual execution plan only.
Actual I/O StatisticsShows 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 and scan operators, but also on spools or on operators that have a potential to spill to tempdb). Actual execution plan only.
Actual Number of BatchesThe number of batches the operator returned. Shows the total number of batches over all executions. Zero when running in row mode. Actual execution plan only.
Actual Number of RowsThe number of rows the operator returned. Shows the total number of rows over all executions. Actual execution plan only.
Actual RebindsThe 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. Actual execution plan only.
Actual RewindsThe 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. Actual execution plan only.
Actual Time StatisticsActual time consumption for the operator, broken down into “Actual Elapsed CPU Time” (which I assume is CPU time) and “Actual Elapsed Time”. In parallel plans, these are further broken down by thread. Actual execution plan only.
Defined ValuesDefinitions for each of the columns that is computed within the operator and added to the output stream.
DescriptionA short description of what the operator does.
Estimated CPU CostAn estimation of how much the CPU usage of the operator will contribute to the total plan cost. Determined during query optimization.
Estimated Execution ModeThe requested execution mode (row mode or batch mode). Determined during query optimization.
Estimated I/O CostAn estimation of how much the I/O required by the operator will contribute to the total plan cost. Determined during query optimization.
Estimated Number of ExecutionsAn 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. Determined during query optimization.
Estimated Number of RowsAn 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.
Estimated Operator CostAn estimation of the total cost of the operator, equal to the sum of Estimated CPU Cost, Estimated I/O Cost, and the cost of the memory requirement for the operator. In Management Studio, this is represented both as a number and as a percentage, which is the ratio between this operator’s estimated cost and the combined total estimated cost of all operators. Determined during query optimization.
Estimated RebindsAn 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.
Estimated RewindsAn 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 SizeAn 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 CostThe combined total Estimated Operator Cost of this operator and all its child nodes (e.g. operators called directly or indirectly by this operator). For the top-left operator of any plan, this is the estimated cost of the total plan. Determined during query optimization.
Logical OperationThe logical operation that the operator is required to carry out when the plan is executed. Is displayed on all operators, nut only relevant for operators that support multiple logical operations. See each operator’s page for a description of each supported logical operation.
Memory FractionsFor 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 Fractions Input: This shows the maximum fraction of memory to be used while processing the Init() call.

  • Memory Fractions Output: This shows the maximum fraction of memory to be used while processing GetNext() calls.
Memory UsageFor 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 exact meaning of the three properties is currently based on assumptions.
  • Input Memory: This probably shows the amount of memory allocated to the operator while processing the Init() call.

  • Output Memory: This probably shows the amount of memory allocated to the operator while processing GetNext() calls.

  • Used Memory: This probably shows the amount of memory actually used by the operator during execution of the query.
Node IDA unique value (within the plan) to identify the node.
Number of ExecutionsAn 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.
Output ListA 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).
ParallelTrue 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 OperationThe name of the operator.
StarJoinInfoThis property is added to all operators that were introduced by the optimizer as part of a star join optimization. It contains the following components:

  • OperationType: Shows what type of operation within the star join optimization rewrite this operator does. Possible values are Fetch, Index Intersection, Index Filter, and Index Lookup.

  • Root: Only present (and set to true) on the operator that is at the root of the plan section for the star join optimization pattern.

For more information, see https://sqlperformance.com/2014/01/sql-plan/starjoininfo-in-execution-plans.

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 nameDescription
Batch Mode supportIn 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.
BlockingMost 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 requirementMost 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 guaranteesOperators 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 awareMost 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 awareThe 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.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close