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.
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.
|Actual Execution Mode||Did the operator run in row mode or in batch mode? Actual execution plan only.|
|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 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 Batches||The 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 Rows||The number of rows the operator returned. Shows the total number of rows over all executions. Actual execution plan only.|
|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. Actual execution plan only.|
|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. Actual execution plan only.|
|Actual Time Statistics||Actual 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 Values||Definitions for each of the columns that is computed within the operator and added to the output stream.|
|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. Determined during query optimization.|
|Estimated Execution Mode||The requested execution mode (row mode or batch mode). Determined during query optimization.|
|Estimated I/O Cost||An 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 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. Determined during query optimization.|
|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.|
|Estimated Operator Cost||An 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 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.|
|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||The 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 Operation||The 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 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 exact meaning of the three properties is currently based on assumptions.
|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.|
|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:
For more information, see https://sqlperformance.com/2014/01/sql-plan/starjoininfo-in-execution-plans.
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.
|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
|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.|