1022
Part VII: Performance Tuning and Optimization
Query Path 5 — Bookmark Lookup
This bookmark lookup query path is a two-edged sword. For queries returning a small num-
ber of records, it’s an acceptable query path, but for the queries that return a signifi cant
amount of records , this query path can signifi cantly hinder performance.To demonstrate a bookmark lookup query path, the following query fi lters by ProductID
while returning all the base table’s columns:SELECT *
FROM Production.WorkOrder
WHERE ProductID = 757;To rephrase the query in pseudo-code, fi nd the rows for Product 757 and give me all the
columns for those rows.There is an index on the ProductID column, so the query optimizer has two possible
options:■ (^) Scan the entire clustered index to access all the columns, and then fi lter the results
to fi nd the right rows. Essentially, this would be the same as Query Path 4.
Or:
■ Perform an index seek on the IX_Workload_ProductID index to fetch the 11
rows. In the process it learns the WorkOrderID values for those 11 rows because
the clustered index key columns are in the leaf level of the nonclustered index.
Then it can index seek those 11 rows from the clustered index to fetch the other
columns.
This jump, from the nonclustered index used to fi nd the rows to the clustered index
to complete the columns needed for the query, is called a bookmark lookup as shown
in Figure 45-10.
The real cost of the bookmark lookup is fi nding the rows that are typically scattered
throughout the base table, which is a clustered index in this case. Locating the 11 rows in
the nonclustered index was a single page hit, but those 11 rows might be on 11 different
pages in the clustered index. With a larger number of selected rows, the problem intensi-
fi es. Selecting 1,000 rows with a bookmark lookup might mean reading three to four pages
from the nonclustered index and then reading more than 1,000 pages from the clustered
index and leaf level. Eventually, SQL Server decides that the bookmark lookup is more
expensive than just scanning the clustered index.
The query execution plan for a bookmark lookup shows the two indexes as data sources for
a nested loop join (as shown in Figure 45-11). For each row that comes from the seek of the
nonclustered index, the nested loop join is requesting the matching rows from the clus-
tered index by calling the key lookup.
c45.indd 1022c45.indd 1022 7/31/2012 10:16:41 AM7/31/2012 10:16:41 AM
http://www.it-ebooks.info