Showplan logical and physical operators

Last modified: June 23, 2022

This topic provides the descriptions of the logical and physical operators.

Execution Plan Icon	Showplan Operator	Description
	Assert	The Assert operator is a physical operator. It serves to verify a condition. If the expression evaluates to NULL, the row is passed through the Assert operator, and the query execution continues. If the returned value is not NULL, the error is thrown. The Assert operator is typically found in the execution plans for INSERT, UPDATE, DELETE, and MERGE statements to ensure that no CHECK and FOREIGN KEY constraints are violated.
	Assign	The Assign operator is used to assign the values to a variable. Assign is a language element.
	Bitmap	The Bitmap operator is a physical operator. It serves to implement bitmap filtering in parallel query plans, which improves query performance when it deals with large amounts of data and the execution plan works in parallel. Bitmap filtering eliminates rows with key values that cannot produce any join records before passing rows through another operator such as the Parallelism operator.
	Clustered Index Delete	The Clustered Index Delete is a physical operator that deletes rows from the clustered index specified in the Argument column of the query execution plan. In case there is the WHERE predicate in the Argument column, only those rows that satisfy that predicate will be deleted.
	Clustered Index Insert	The Clustered Index Insert is a physical operator. As its name implies, it is used to insert rows into the clustered index. The rows to be inserted can be taken from the operator’s child subtree or the Insert operator itself.
	Clustered Index Merge	The Clustered Index Merge is a physical operator that is used to apply 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 Scan	The Clustered Index Scan is a logical and physical operator that is used to read the clustered index specified in the Argument column of the query execution plan. In case there is the WHERE predicate, only those rows that satisfy the predicate will be returned.
	Clustered Index Seek	Clustered Index Seek is a logical and physical operator that uses the seeking ability of indexes to retrieve rows from a clustered index. The Argument column contains the name of the clustered index and the SEEK predicate. The storage engine uses the index to process only those rows that satisfy the predicate.
	Clustered Index Update	The Clustered Index Update is a logical and physical operator that is used to update input rows in the clustered index specified in the Argument column.
	Collapse	The Collapse operator is a logical and physical operator that is used to optimize update processing. The query processor replaces the delete and insert operations for the same key value with a single more efficient update operation.
	Columnstore Index Scan	The Columnstore Index Scan operator scans the columnstore index specified in the Argument column of the query execution plan.
	Compute Scalar	Compute Scalar is a logical and physical operator that is used to calculate a new value from the existing row value by performing a scalar computation operation. The produced computed scalar value can be returned to the user, referenced elsewhere in the query, or both. An example of both is in a filter predicate or join predicate.
	Concatenation	Concatenation is a logical and physical operator that scans multiple inputs and returns each row scanned. It is typically used to implement the Transact-SQL UNION ALL construct. The Concatenation physical operator joins two or more distinct inputs into one output.
	Constant Scan	Constant Scan is a logical and physical operator that is used to introduce a constant row into a query. To add columns to the row produced by a Constant Scan, a Compute Scalar operator is often used.
	Catchall	The catchall icon is shown when a suitable icon for the iterator cannot be found by the logic that produces graphical showplans. The catchall icon does not necessarily indicate an error condition. There are three catchall icons: blue (for iterators), orange (for cursors), and green (for Transact-SQL language elements).
	Delete	The Delete operator removes rows from an object. If the Argument column contains a predicate, only those rows that satisfy the predicate will be deleted.
	Deleted Scan	The Deleted Scan operator is only found in execution plans for code in triggers. It scans the deleted table within a trigger.
	Dynamic	The Dynamic operator represents the declaration of a dynamic cursor that allows detecting all changes made by others.
	Fetch Query	The Fetch Query operator is used to retrieve rows within FETCH statements.
	Filter	The Filter operator scans input rows, returning only those rows for which the predicate evaluates to True.
	Hash Match	The Hash Match operator builds a hash table from its first input by computing a hash value for each row, then uses that hash table to either join to its second input, or produce aggregated values.
	Insert	The Insert logical operator inserts rows from its input into the object specified in the Argument column and is represented by the Table Insert, Index Insert, or Clustered Index Insert operator.
	Inserted Scan	The Inserted Scan is a logical and physical operator that scans the inserted table. It is used in triggers only.
	Key Lookup	The Key Lookup operator reads a single row from a clustered index, based on a key value that was retrieved from a nonclustered index on the same table. Key Lookup is always accompanied by a Nested Loops operator. The Key Lookup operator in a query plan indicates that the query performance can be tuned, for example, by adding a covering index.
	Keyset	The Keyset operator represents the declaration of a keyset cursor that can detect updates but not inserts made by others.
	Log Row Scan	The Log Row Scan is a logical and physical operator used to scan the transaction log.
	Merge Interval	The Merge Interval is a logical and physical operator. It combines multiple sets of potentially overlapping intervals into lesser but nonoverlapping intervals that are then used to seek index entries.
	Merge Join	The Merge Join operator joins two inputs that are ordered by the join key(s) and thus 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.
	Nested Loops	The Nested Loops is a physical operator that performs the inner join, left outer join, left semi join, and left anti semi join logical operations. It joins two inputs by repeatedly executing the second input for each row in the first input.
	Nonclustered Index Delete	Nonclustered Index Delete is a physical operator that deletes input rows from a nonclustered index.
	Index Insert	Index Insert is a physical operator that inserts rows from its input into the nonclustered index.
	Index Scan	Index Scan is a logical and physical operator that reads all data from a nonclustered index.
	Index Seek	Index Seek is a logical and physical operator that uses the seeking ability of indexes to find a specific row in a nonclustered index based on a key value.
	Index Spool	The Index Spool operator stores its input rows in an internal, indexed worktable that is stored in the tempdb database and exists only for the lifetime of the query. This indexed worktable can then be used to re-process specific subsets of the data instead of rescanning the input.
	Nonclustered Index Update	The Nonclustered Index Update is a physical operator that updates rows in a nonclustered index.
	Online Index Insert	Online Index Insert is a physical operator that indicates that an index create, alter, or drop operation is performed while keeping the index online.
	Parallelism	The Parallelism operator also known as Exchange Iterator is a physical operator that manages the distribution of rows between threads in parallel plans.
	Parameter Table Scan	Parameter Table Scan is a logical and physical operator that scans a table that is acting as a parameter in the current query. Typically the Parameter Table Scan operators are used for the INSERT queries within a stored procedure.
	Population Query	The Population Query operator represents the query to be used for the OPEN cursor statement.
	Refresh Query	The Refresh Query operator fetches current data for rows in the fetch buffer.
	Remote Delete	Remote Delete is a logical and physical operator that deletes rows from a remote object.
	Remote Index Scan	Remote Index Scan is a logical and physical operator that scans the remote index.
	Remote Index Seek	Remote Index Seek is a logical physical operator that uses the seeking ability of a remote index object to retrieve rows.
	Remote Insert	Remote Insert is a logical and physical operator that inserts rows into a remote object.
	Remote Query	Remote Query is a logical and physical operator that submits a query to a remote source for execution there. The operator will then return all rows produced by the remote source.
	Remote Scan	Remote Scan is a logical and physical operator that reads all data from a remote object.
	Remote Update	Remote Update is a logical and physical operator used to update rows in a remote object.
	RID Lookup	RID Lookup is a physical operator that reads a row from a heap table, based on an RID value that was retrieved from a nonclustered index on the same table.
	Row Count Spool	Row Count Spool is a physical operator that scans the input, counts the number of rows in it, and then returns the same number of empty rows.
	Segment	Segment is a physical and a logical operator that divides the input set into segments based on the value of one or more columns and marks the start of each new segment in the data stream by inspecting specific columns for a change in their data.
	Sequence	Sequence is a logical and physical operator that processes two or more inputs, in order, returning only rows returned from the final input.
	Sequence Project	Sequence Project is a logical and physical operator that 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 and then outputs one segment at a time.
	Snapshot	The Snapshot operator represents the declaration of a static cursor that does not see changes made by others.
	Sort	Sort is a logical and physical operator that reads all input rows, sorts them, and then returns them in the specified order.
	Split	Split is a logical and physical operator that optimizes update processing by replacing update operations with the corresponding delete and insert operations.
	Stream Aggregate	Stream Aggregate is a physical operator that calculates aggregation results by reading a sorted input stream and returning a single row for each set of rows with the same key value.
	Switch	Switch is both a logical and physical operator that executes only one of its inputs; which input is executed is determined at runtime.
	Table Delete	Table Delete is a physical operatorused to delete rows from a heap table, and optionally from one or more nonclustered indexes at the same time.
	Table Insert	Table Insert is a physical operator that inserts rows into a heap table, and optionally into one or more nonclustered indexes at the same time.
	Table Merge	Table Merge is a physical operator that applies a merge data stream to a heap table, and optionally applies the corresponding changes to one or more nonclustered indexes at the same time.
	Table Scan	Table Scan is a logical and physical operator that reads all data from a heap table, in allocation order.
	Table Spool	Table Spool is a physical operator that scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database and exists only for the lifetime of the query; this worktable can then be used to re-process the same data.
	Table Update	Table Update physical operator that updates input rows in a heap table, and optionally in one or more nonclustered indexes at the same time.
	Table-valued Function	The Table-valued Function operator executes a table-valued function and stores the results in a temporary table.
	Top	Top is a logical and physical operator that scans the input, returning only the first specified number or percent of rows, optionally skipping the first few and optionally adding extra rows based on ties in specified columns.
	UDX	UDX operators are both logical and physical operators that implement XQuery and XPath operations in SQL Server.
	Update	Update is a logical operator that updates rows from its input. The physical operator is Table Update, Index Update, or Clustered Index Update.