TSQL Execution Plan

Hash Match

use each row from the top input to build a hash table, and each row from the bottom input to probe into the hash table, outputting all matching rows.

Clustered Index Scan

Scanning a clustered index, entirely or only a range.

Clustered Index Seek

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, the Key Lookup operator provides bookmark lookup functionality.

Clustered Index Seek is a logical and physical operator.

Compute scalar

Compute new values from existing values in a row

Stream aggregate

Compute summary values for groups of rows in a suitably sorted stream. 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.

Sort

Sort the input

Nested loops

For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.

Merge join

Match rows from two suitably sorted input tables and exploiting their sort order.

table spool

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.

Resources

• icons