Common Execution-Plan Operators
| Operator | Meaning |
|---|---|
| Table Scan | Reads the entire heap table. It can be expensive for large tables. |
| Clustered Index Scan | Reads many or all rows from a clustered index. |
| Index Scan | Reads many or all rows from a nonclustered index. |
| Index Seek | Directly finds the required rows using an index. Usually efficient. |
| Key Lookup | Retrieves additional columns from the clustered index after using a nonclustered index. Expensive when repeated many times. |
| RID Lookup | Retrieves additional columns from a heap table. |
| Nested Loops | Compares rows by repeatedly searching the inner input. Suitable for small result sets. |
| Hash Match | Uses a hash table for joins or aggregation. Useful for large, unsorted datasets but may consume more memory. |
| Merge Join | Joins two sorted inputs. Efficient when both inputs are already sorted. |
| Sort | Sorts rows for ORDER BY, joins or other operations. It may use tempdb when memory is insufficient. |
| Filter | Removes rows that do not satisfy a condition. |
| Stream Aggregate | Performs aggregation on sorted data. |
| Hash Aggregate | Performs aggregation using a hash table. |
| Compute Scalar | Calculates expressions or derived column values. |
| Spool | Temporarily stores rows for reuse, usually in tempdb. |
| Parallelism | Distributes or combines work across multiple CPU threads. |
Key points
-
An Index Seek is generally efficient, but it is not always better than a scan.
-
A scan can be suitable when the query needs most of the table.
-
Look for expensive lookups, incorrect row estimates, spills, warnings and operators processing many rows—not only the highest cost percentage.