SQL Server includes both logical and physical operations in query plans, and we examined physical operations for a consistent study with other DBMSs. SQL Server is not open-source, so we examined its documents to study query plans. Note that the following descriptions are copied from SQL Server documents.
| Operation | Category | Reference | Description |
|---|---|---|---|
| Adaptive Join | Join | Link | The Adaptive Join operator enables the choice of a hash join or nested loops join method to be deferred until after the first input has been scanned. The Adaptive Join operator is a physical operator. For more information, see Understanding Adaptive joins. |
| Aggregate | Folder | Link | The Aggregate operator calculates an expression containing MIN, MAX, SUM, COUNT or AVG. The Aggregate operator can be a logical operator or a physical operator. |
| Assert | Executor | Link | The Assert operator verifies a condition. For example, it validates referential integrity or ensures that a scalar subquery returns one row. For each input row, the Assert operator evaluates the expression in the Argument column of the execution plan. If this expression evaluates to NULL, the row is passed through the Assert operator and the query execution continues. If this expression evaluates to a nonnull value, the appropriate error will be raised. The Assert operator is a physical operator. |
| Bitmap | Executor | Link | SQL Server uses the Bitmap operator to implement bitmap filtering in parallel query plans. Bitmap filtering speeds up query execution by eliminating rows with key values that cannot produce any join records before passing rows through another operator such as the Parallelism operator. A bitmap filter uses a compact representation of a set of values from a table in one part of the operator tree to filter rows from a second table in another part of the tree. By removing unnecessary rows early in the query, subsequent operators have fewer rows to work with, and the overall performance of the query improves. The optimizer determines when a bitmap is selective enough to be useful and in which operators to apply the filter. Bitmap is a physical operator. |
| Clustered Index Delete | Consumer | Link | The Clustered Index Delete operator deletes rows from the clustered index specified in the Argument column of the query execution plan. If a WHERE:() predicate is present in the Argument column, then only those rows that satisfy the predicate are deleted.Clustered Index Delete is a physical operator. |
| Clustered Index Insert | Consumer | Link | The Clustered Index Insert Showplan operator inserts rows from its input into the clustered index specified in the Argument column. The Argument column also contains a SET:() predicate, which indicates the value to which each column is set. If Clustered Index Insert has no children for insert values, the row inserted is taken from the Insert operator itself.Clustered Index Insert is a physical operator. |
| Clustered Index Merge | Consumer | Link | The Clustered Index Merge operator applies a merge data stream to a clustered index. The operator deletes, updates, or inserts rows from the clustered index specified in the Argument column of the operator. The actual operation performed depends on the runtime value of the ACTION column specified in the Argument column of the operator. Clustered Index Merge is a physical operator. |
| Clustered Index Scan | Producer | Link | The Clustered Index Scan operator scans the clustered index specified in the Argument column of the query execution plan. When an optional WHERE:() predicate is present, only those rows that satisfy the predicate are returned. If the Argument column contains the ORDERED clause, the query processor has requested that the output of the rows be returned in the order in which the clustered index has sorted it. If the ORDERED clause is not present, the storage engine scans the index in the optimal way, without necessarily sorting the output. Clustered Index Scan is a logical and physical operator. |
| Clustered Index Seek | Producer | Link | The Clustered Index Seek operator uses the seeking ability of indexes to retrieve rows from a clustered index. The Argument column contains the name of the clustered index being used and the SEEK:() predicate. The storage engine uses the index to process only those rows that satisfy this SEEK:() predicate. It can also include a WHERE:() predicate where the storage engine evaluates against all rows that satisfy the SEEK:() predicate, but this is optional and does not use indexes to complete this process. If the Argument column contains the ORDERED clause, the query processor has determined that the rows must be returned in the order in which the clustered index has sorted them. If the ORDERED clause is not present, the storage engine searches the index in the optimal way, without necessarily sorting the output. Allowing the output to retain its ordering can be less efficient than producing nonsorted output. When the keyword LOOKUP appears, then a bookmark lookup is being performed. In SQL Server 2008 (10.0.x) and later versions, the Key Lookup operator provides bookmark lookup functionality. Clustered Index Seek is a logical and physical operator. |
| Clustered Index Update | Consumer | Link | The Clustered Index Update operator updates input rows in the clustered index specified in the Argument column.If a WHERE:() predicate is present, only those rows that satisfy this predicate are updated. If a SET:() predicate is present, each updated column is set to this value. If a DEFINE:() predicate is present, the values that this operator defines are listed. These values may be referenced in the SET clause or elsewhere within this operator and elsewhere within this query. Clustered Index Update is a logical and physical operator. |
| Collapse | Consumer | Link | The Collapse operator optimizes update processing. When an update is performed, it can be split (using the Split operator) into a delete and an insert. The Argument column contains a GROUP BY:() clause that specifies a list of key columns. If the query processor encounters adjacent rows that delete and insert the same key values, it replaces these separate operations with a single more efficient update operation. Collapse is a logical and physical operator. |
| Compute Scalar | Executor | Link | The Compute Scalar operator evaluates an expression to produce a computed scalar value. This may then be returned to the user, referenced elsewhere in the query, or both. An example of both is in a filter predicate or join predicate. Compute Scalar is a logical and physical operator. Compute Scalar operators that appear in Showplans generated by SET STATISTICS XML might not contain the RunTimeInformation element. In graphical Showplans, Actual Rows, Actual Rebinds, and Actual Rewinds might be absent from the Properties window when the Include Actual Execution Plan option is selected in SQL Server Management Studio. When this occurs, it means that although these operators were used in the compiled query plan, their work was performed by other operators in the run-time query plan. Also note that the number of executes in Showplan output generated by SET STATISTICS PROFILE is equivalent to the sum of rebinds and rewinds in Showplans generated by SET STATISTICS XML. |
| Concatenation | Combinator | Link | The Concatenation operator scans multiple inputs, returning each row scanned. Concatenation is typically used to implement the Transact-SQL UNION ALL construct. The Concatenation physical operator has two or more inputs and one output. Concatenation copies rows from the first input stream to the output stream, then repeats this operation for each additional input stream. Concatenation is a logical and physical operator. |
| Cursor | Executor | Link | The Cursor logical and physical operators are used to describe how a query or update involving cursor operations is executed. The physical operators describe the physical implementation algorithm used to process the cursor; for example, using a keyset-driven cursor. Each step in the execution of a cursor involves a physical operator. The logical operators describe a property of the cursor, such as the cursor is read only. Logical operators include Asynchronous, Optimistic, Primary, Read Only, Scroll Locks, and Secondary and Synchronous. Physical operators include Dynamic, Fetch Query, Keyset, Population Query, Refresh Query and Snapshot. |
| Foreign Key References Check | Executor | Link | The Foreign Key References Check operator performs referential integrity checks in place, by comparing the modified row to the rows in the referencing tables to verify that the modification will not break the referential integrity. The Foreign Key References Check operator is used when more than 253 foreign key references exist on the same primary or unique key. Foreign Key References Check is a logical and physical operator. |
| Hash Match | Executor | Link | The Hash Match operator builds a hash table by computing a hash value for each row from its build input. A HASH:() predicate with a list of columns used to create a hash value appears in the Argument column. Then, for each probe row (as applicable), it computes a hash value (using the same hash function) and looks in the hash table for matches. If a residual predicate is present (identified by RESIDUAL:() in the Argument column), that predicate must also be satisfied for rows to be considered a match. Behaviour depends on the logical operation being performed: -For any joins, use the first (top) input to build the hash table and the second (bottom) input to probe the hash table. Output matches (or nonmatches) as dictated by the join type. If multiple joins use the same join column, these operations are grouped into a hash team. -For the distinct or aggregate operators, use the input to build the hash table (removing duplicates and computing any aggregate expressions). When the hash table is built, scan the table and output all entries. -For the union operator, use the first input to build the hash table (removing duplicates). Use the second input (which must have no duplicates) to probe the hash table, returning all rows that have no matches, then scan the hash table and return all entries. Hash Match is a physical operator. For more information, see Understanding Hash joins. |
| Index Insert | Consumer | Link | The Index Insert operator inserts rows from its input into the nonclustered index specified in the Argument column. The Argument column also contains a SET:() predicate, which indicates the value to which each column is set. Index Insert is a physical operator. |
| Index Scan | Producer | Link | The Index Scan operator retrieves all rows from the nonclustered index specified in the Argument column. If an optional WHERE:() predicate appears in the Argument column, only those rows that satisfy the predicate are returned. Index Scan is a logical and physical operator. |
| Index Seek | Producer | Link | The Index Seek operator uses the seeking ability of indexes to retrieve rows from a nonclustered index. The Argument column contains the name of the nonclustered index being used. It also contains the SEEK:() predicate. The storage engine uses the index to process only those rows that satisfy the SEEK:() predicate. It optionally may include a WHERE:() predicate, which the storage engine will evaluate against all rows that satisfy the SEEK:() predicate (it does not use the indexes to do this). If the Argument column contains the ORDERED clause, the query processor has determined that the rows must be returned in the order in which the nonclustered index has sorted them. If the ORDERED clause is not present, the storage engine searches the index in the optimal way (which does not guarantee that the output will be sorted). Allowing the output to retain its ordering may be less efficient than producing nonsorted output. Index Seek is a logical and physical operator. |
| Index Spool | Producer | Link | The Index Spool physical operator contains a SEEK:() predicate in the Argument column. The Index Spool operator scans its input rows, placing a copy of each row in a hidden spool file (stored in the tempdb database and existing only for the lifetime of the query), and builds a nonclustered index on the rows. This allows you to use the seeking capability of indexes to output only those rows that satisfy the SEEK:() predicate. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input. |
| Insert | Consumer | Link | The Insert logical operator inserts each row from its input into the object specified in the Argument column. The physical operator is either the Table Insert, Index Insert, or Clustered Index Insert operator. |
| Inserted Scan | Consumer | Link | The Inserted Scan operator scans the inserted table. Inserted Scan is a logical and physical operator. |
| Log Row Scan | Producer | Link | The Log Row Scan operator scans the transaction log. Log Row Scan is a logical and physical operator. |
| Merge Interval | Combinator | Link | The Merge Interval operator merges multiple (potentially overlapping) intervals to produce minimal, nonoverlapping intervals that are then used to seek index entries. This operator typically appears above one or more Compute Scalar operators over Constant Scan operators, which construct the intervals (represented as columns in a row) that this operator merges. Merge Interval is a logical and physical operator. |
| Merge Join | Join | Link | The Merge Join operator performs the inner join, left outer join, left semi join, left anti semi join, right outer join, right semi join, right anti semi join, and union logical operations. In the Argument column, the Merge Join operator contains a MERGE:() predicate if the operation is performing a one-to-many join, or a MANY-TO-MANY MERGE:() predicate if the operation is performing a many-to-many join. The Argument column also includes a comma-separated list of columns used to perform the operation. The Merge Join operator requires two inputs sorted on their respective columns, possibly by inserting explicit sort operations into the query plan. Merge join is particularly effective if explicit sorting is not required, for example, if there is a suitable B-tree index in the database or if the sort order can be exploited for multiple operations, such as a merge join and grouping with roll up. Merge Join is a physical operator. For more information, see Understanding Merge joins. |
| Nested Loops | Join | Link | The Nested Loops operator performs the inner join, left outer join, left semi join, and left anti semi join logical operations. Nested loops joins perform a search on the inner table for each row of the outer table, typically using an index. The query processor decides, based on anticipated costs, whether to sort the outer input in order to improve locality of the searches on the index over the inner input. Any rows that satisfy the (optional) predicate in the Argument column are returned as applicable, based on the logical operation being performed. When the OPTIMIZED attribute is set to True, it means that an Optimized Nested Loops (or Batch Sort) is used. Nested Loops is a physical operator. For more information, see Understanding Nested Loops joins. |
| Nonclustered Index Delete | Consumer | Link | The Nonclustered Index Delete operator deletes input rows from the nonclustered index specified in the Argument column. Nonclustered Index Delete is a physical operator. |
| Nonclustered Index Update | Consumer | Link | The Nonclustered Index Update physical operator updates rows from its input in the nonclustered index specified in the Argument column. If a SET:() predicate is present, each updated column is set to this value. Nonclustered Index Update is a physical operator. |
| Online Index Insert | Consumer | Link | The Online Index Insert physical operator indicates that an index create, alter, or drop operation is performed online. That is, the underlying table data remains available to users during the index operation. |
| Parallelism | Executor | Link | The Parallelism operator (or Exchange Iterator) performs the distribute streams, gather streams, and repartition streams logical operations. The Argument columns can contain a PARTITION COLUMNS:() predicate with a comma-separated list of the columns being partitioned. The Argument columns can also contain an ORDER BY:() predicate, listing the columns to preserve the sort order for during partitioning. Parallelism is a physical operator. For more information about the Parallelism operator, see Craig Freedman’s blog series. Note: If a query has been compiled as a parallel query, but at run time it is run as a serial query, the Showplan output generated by SET STATISTICS XML or by using the Include Actual Execution Plan option in SQL Server Management Studio will not contain the RunTimeInformation element for the Parallelism operator. In SET STATISTICS PROFILE output, the actual row counts and actual number of executes will display zeroes for the Parallelism operator. When either condition occurs, it means that the Parallelism operator was only used during query compilation and not in the run-time query plan. Note that sometimes parallel query plans are run in serial if there is a high concurrent load on the server. |
| Parameter Table Scan | Producer | Link | The Parameter Table Scan operator scans a table that is acting as a parameter in the current query. Typically, this is used for INSERT queries within a stored procedure. Parameter Table Scan is a logical and physical operator. |
| Partial Aggregate | Folder | Link | Partial Aggregate is used in parallel plans. It applies an aggregation function to as many input rows as possible so that writing to disk (known as a “spill”) is not necessary. Hash Match is the only physical operator (iterator) that implements partial aggregation. Partial Aggregate is a logical operator. |
| Remote Delete | Consumer | Link | The Remote Delete operator deletes the input rows from a remote object. Remote Delete is a logical and physical operator. |
| Remote Index Scan | Producer | Link | The Remote Index Scan operator scans the remote index specified in the Argument column. Remote Index Scan is a logical and physical operator. |
| Remote Index Seek | Producer | Link | The Remote Index Seek operator uses the seeking ability of a remote index object to retrieve rows. The Argument column contains the name of the remote index being used and the SEEK:() predicate. Remote Index Seek is a logical physical operator. |
| Remote Insert | Consumer | Link | The Remote Insert operator inserts the input rows into a remote object. Remote Insert is a logical and physical operator. |
| Remote Query | Executor | Link | The Remote Query operator submits a query to a remote source. The text of the query sent to the remote server appears in the Argument column. Remote Query is a logical and physical operator. |
| Remote Scan | Producer | Link | The Remote Scan operator scans a remote object. The name of the remote object appears in the Argument column. Remote Scan is a logical and physical operator. |
| Remote Update | Consumer | Link | The Remote Update operator updates the input rows in a remote object. Remote Update is a logical and physical operator. |
| RID Lookup | Producer | Link | RID Lookup is a bookmark lookup on a heap using a supplied row identifier (RID). The Argument column contains the bookmark label used to look up the row in the table and the name of the table in which the row is looked up. RID Lookup is always accompanied by a NESTED LOOP JOIN. RID Lookup is a physical operator. For more information about bookmark lookups, see “Bookmark Lookup” on the MSDN SQL Server blog. |
| Row Count Spool | Producer | Link | The Row Count Spool operator scans the input, counting how many rows are present and returning the same number of rows without any data in them. This operator is used when it is important to check for the existence of rows, rather than the data contained in the rows. For example, if a Nested Loops operator performs a left semi join operation and the join predicate applies to inner input, a row count spool may be placed at the top of the inner input of the Nested Loops operator. Then the Nested Loops operator can determine how many rows are output by the row count spool (because the actual data from the inner side is not needed) to determine whether to return the outer row. Row Count Spool is a physical operator. |
| Segment | Executor | Link | Segment is a physical and a logical operator. It divides the input set into segments based on the value of one or more columns. These columns are shown as arguments in the Segment operator. The operator then outputs one segment at a time. |
| Sequence | Executor | Link | The Sequence operator drives wide update plans. Functionally, it executes each input in sequence (top to bottom). Each input is usually an update of a different object. It returns only those rows that come from its last (bottom) input. Sequence is a logical and physical operator. |
| Sequence Project | Executor | Link | The Sequence Project operator adds columns to perform computations over an ordered set. It divides the input set into segments based on the value of one or more columns. The operator then outputs one segment at a time. These columns are shown as arguments in the Sequence Project operator. Sequence Project is a logical and physical operator. |
| Sort | Combinator | Link | The Sort operator sorts all incoming rows. The Argument column contains either a DISTINCT ORDER BY:() predicate if duplicates are removed by this operation, or an ORDER BY:() predicate with a comma-separated list of the columns being sorted. The columns are prefixed with the value ASC if the columns are sorted in ascending order, or the value DESC if the columns are sorted in descending order. Sort is a logical and physical operator. |
| Split | Executor | Link | The Split operator is used to optimize update processing. It splits each update operation into a delete and an insert operation. Split is a logical and physical operator. |
| Stream Aggregate | Folder | Link | The Stream Aggregate operator groups rows by one or more columns and then calculates one or more aggregate expressions returned by the query. The output of this operator can be referenced by later operators in the query, returned to the client, or both. The Stream Aggregate operator requires input ordered by the columns within its groups. The optimizer will use a Sort operator prior to this operator if the data is not already sorted due to a prior Sort operator or due to an ordered index seek or scan. In the SHOWPLAN_ALL statement or the graphical execution plan in SQL Server Management Studio, the columns in the GROUP BY predicate are listed in the Argument column, and the aggregate expressions are listed in the Defined Values column. Stream Aggregate is a physical operator. |
| Switch | Executor | Link | Switch is a special type of concatenation iterator that has n inputs. An expression is associated with each Switch operator. Depending on the return value of the expression (between 0 and n-1), Switch copies the appropriate input stream to the output stream. One use of Switch is to implement query plans involving fast forward cursors with certain operators such as the TOP operator. Switch is both a logical and physical operator. |
| Table Delete | Consumer | Link | The Table Delete physical operator deletes rows from the table specified in the Argument column of the query execution plan. |
| Table Insert | Consumer | Link | The Table Insert operator inserts rows from its input into the table specified in the Argument column of the query execution plan. The Argument column also contains a SET:() predicate, which indicates the value to which each column is set. If Table Insert has no children for insert values, then the row inserted is taken from the Insert operator itself. Table Insert is a physical operator. |
| Table Merge | Consumer | Link | The Table Merge operator applies a merge data stream to a heap. The operator deletes, updates, or inserts rows in the table specified in the Argument column of the operator. The actual operation performed depends on the run-time value of the ACTION column specified in the Argument column of the operator. Table Merge is a physical operator. |
| Table Scan | Producer | Link | The Table Scan operator retrieves all rows from the table specified in the Argument column of the query execution plan. If a WHERE:() predicate appears in the Argument column, only those rows that satisfy the predicate are returned. Table Scan is a logical and physical operator. |
| Table Spool | Executor | Link | The Table Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database and existing only for the lifetime of the query. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input. Table Spool is a physical operator. |
| Table Update | Consumer | Link | The Table Update physical operator updates input rows in the table specified in the Argument column of the query execution plan. The SET:() predicate determines the value of each updated column. These values may be referenced in the SET clause or elsewhere within this operator as well as elsewhere within this query. |
| Table-valued Function | Executor | Link | The Table-valued Function operator evaluates a table-valued function (either Transact-SQL or CLR), and stores the resulting rows in the tempdb database. When the parent iterators request the rows, Table-valued Function returns the rows from tempdb. Queries with calls to table-valued functions generate query plans with the Table-valued Function iterator. Table-valued Function can be evaluated with different parameter values: - Table-valued Function XML Reader inputs an XML BLOB as a parameter and produces a rowset representing XML nodes in XML document order. Other input parameters may restrict XML nodes returned to a subset of XML document. -Table Valued Function XML Reader with XPath filter is a special type of XML Reader Table-valued Function that restricts output to XML nodes satisfying an XPath expression. Table-valued Function is a logical and physical operator. |
| Top | Producer | Link | The Top operator scans the input, returning only the first specified number or percent of rows, possibly based on a sort order. The Argument column can contain a list of the columns that are being checked for ties. In update plans, the Top operator is used to enforce row count limits. Top is a logical and physical operator. |
| UDX | Executor | Link | Extended Operators (UDX) implement one of many XQuery and XPath operations in SQL Server. All UDX operators are both logical and physical operators. Extended operator (UDX) FOR XML is used to serialize the relational row set it inputs into XML representation in a single BLOB column in a single output row. It is an order sensitive XML aggregation operator. Extended operator (UDX) XML SERIALIZER is an order sensitive XML aggregation operator. It inputs rows representing XML nodes or XQuery scalars in XML document order and produces a serialized XML BLOB in a single XML column in a single output row. Extended operator (UDX) XML FRAGMENT SERIALIZER is a special type of XML SERIALIZER that is used for processing input rows representing XML fragments being inserted in XQuery insert data modification extension. Extended operator (UDX) XQUERY STRING evaluates the XQuery string value of input rows representing XML nodes. It is an order sensitive string aggregation operator. It outputs one row with columns representing the XQuery scalar that contains string value of the input. Extended operator (UDX) XQUERY LIST DECOMPOSER is an XQuery list decomposition operator. For each input row representing an XML node it produces one or more rows each representing XQuery scalar containing a list element value if the input is of XSD list type. Extended operator (UDX) XQUERY DATA evaluates the XQuery fn:data() function on input representing XML nodes. It is an order sensitive string aggregation operator. It outputs one row with columns representing XQuery scalar that contains the result of fn:data(). Extended operator XQUERY CONTAINS evaluates the XQuery fn:contains() function on input representing XML nodes. It is an order sensitive string aggregation operator. It outputs one row with columns representing XQuery scalar that contains the result of fn:contains(). Extended operator UPDATE XML NODE updates XML node in the XQuery replace data modification extension in the modify() method on XML type. |
| Update | Consumer | Link | The Update operator updates each row from its input in the object specified in the Argument column of the query execution plan. Update is a logical operator. The physical operator is Table Update, Index Update, or Clustered Index Update. |
| Window Spool | Producer | Link | The Window Spool operator expands each row into the set of rows that represents the window associated with it. In a query, the OVER clause defines the window within a query result set and a window function then computes a value for each row in the window. Window Spool is a logical and physical operator. |