Professional SQL Server® 2005 Performance Tuning

9.6. Tuning T-SQL to Use Indexes

The following sections cover techniques to get the most out of the indexing that is available in SQL Server. A skillful use of indexing will bolster the performance of many poor-performing queries with minimal impact to SQL Server's resources.

9.6.1. Minimizing Bookmark Lookups

Bookmark lookups occur when a WHERE predicate uses a non-clustered index to access the data in the table, but the index doesn't have all the columns logically to evaluate nor those necessary to return the selection. In the best-case scenarios, you either search against the clustered index, because this contains the data pages themselves, or use the smaller non-clustered indexes if everything you need to search and retrieve is there. Even if not everything you need is in the non-clustered index, it might still be used for seeking operations, although a subsequent lookup must occur to resolve the data page to retrieve the additional columns selected. This resolution can be faster if the non-clustered index includes a reference to a clustered index or slower if seeking on a heap with a row identifier. The need to make the additional I/O read is the main reason that bookmarks should be avoided if possible, but this needs to be kept in perspective. The alternative to a non-clustered index bookmark lookup is a clustered index scan, which is a table scan with help. Even SQL Server will give up and revert to a clustered index scan if the optimizer deems the bookmark lookup operation ...