Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 257

Index Scan
Index Scan is a logical and physical operator. The Index Scan operator works against a non-clustered
index. Just like the table scan, it retrieves all rows, except this time it from a non-clustered index. If a
WHERE clause is present then each row is evaluated based on the WHERE clause. All evaluations
returning true will allow the row to be returned.
Index Seek
Index Seek is a logical and physical operator. The Index Seek operator uses the power of indexes to
retrieve rows. SQL Server uses the index to choose only those rows that match the specified predicate.
Unlike the Index Scan, not all the rows in the index need to be evaluated. Only the rows that match the
search predicate are chosen. Those matches are then outputted.
Clustered Index Scan
Clustered Index Scan is a logical and physical operator. The Clustered Index Scan operator works against
the clustered index of a table. Just like the table scan, it retrieves all rows, except this time it retrieves the
rows from the clustered index. If a WHERE clause is present then each row is evaluated based on the WHERE
clause.
All evaluations returning true will allow the row to be returned.
Clustered Index Seek
Clustered Index Seek is a logical and physical operator. The Clustered Index Seek operator uses the
power of indexes to retrieve rows. SQL Server uses the index to choose only those rows that match the
specified predicate. Unlike the Clustered Index Scan, not all the rows in the index need to be evaluated.
Only the rows that match the search predicate are chosen. Those matches are then output.
LookupOperators
A lookup operator performs the task of locating a row of data in the data pages.
Bookmark Lookup (2000 Only)
Bookmark Lookup is no longer supported in SQL Server 2005. Instead, for heap tables, the RID Lookup
has taken its place. For clustered tables this is replaced by a clustered index seek that includes a
lookup operation.
RID Lookup
RID Lookup is a physical operator. RID Lookup is a lookup on a heap table only. It does not apply if the
table has a clustered index. This operator actually looks up the rows in a table's data pages by using a row
identifier. This can be an expensive operation. The reason is that iterating through the input will force
the lookup to perform random I/O operations. These are expensive because more physical interaction
between the hard drives and SQL Server has to occur.
InterpretingaQueryPlan
Now that the highlights of the graphical plan have been described, you should be well equipped to start
using this information to interpret plans.