Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 135

➤

Seek operator — The seek operator uses the index to i nd matching rows; this can be
either a single value, a small set of values, or a range of values. When the query needs only
a relatively small set of rows, seek is signii cantly faster than scan to i nd matching rows.
However, when the number of rows returned exceeds 20 percent of the table, the cost of
seek will approach that of scan; and when nearly the whole table is required, scan will
perform better than seek.

There are seek operator variants for a clustered index seek and a nonclustered index seek.

Lookup Operators
Lookup operators perform the task of i nding a single row of data. The following is a list of com-
mon operators:

➤

Bookmark lookup — Bookmark lookup is seen only in SQL Server 2000 and earlier. It's
the way that SQL Server looks up a row using a clustered index. In SQL Server 2012 this is
done using either Clustered Index Seek, RID lookup, or Key Lookup.

➤

Key lookup — Key lookup is how a single row is returned when the table has a clustered
index. In contrast with dealing with a heap, the lookup is done using the clustering key. The
key lookup operator was added in SQL Server 2005 SP2. Prior to this, and currently when
viewing the plan in text or XML format, the operator is shown as a clustered index seek
with the keyword lookup.

➤

RID lookup — RID lookup is how a single row is looked up in a heap. RID refers to the
internal unique r ow id entii er (hence RID), which is used to look up the row.

Reading Query Plans
Unlike reading a typical topic such as this one, whereby reading is done from top left to bottom
right (unless you're reading a translation for which the language is read in reverse), query plans in all
forms are read bottom right to top left.

Once you have downloaded and installed the sample database, to make the examples more
interesting you need to remove some of the indexes that the authors of AdventureWorks added for
you. To do this, you can use either your favorite T-SQL scripting tool or the SSMS scripting features,
or run the AW2012 _ person _ drop _ indexes . sql sample script (available on the topic's website in the
Chapter 5 Samples folder, which also contains a script to recreate the indexes if you want to return
the AdventureWorks2012 database to its original structure). This script drops all the indexes on the
person.person table except for the primary key constraint.

After you have done this, you can follow along with the examples, and you should see the same
results.

NOTE Because you are looking at the inner workings of the Query Optimizer,
and because this is a feature of SQL Server that is constantly evolving, installing
any service pack or patch can alter the behavior of the Query Optimizer, and
therefore display different results.