SQL SERVER – Remove Bookmark Key Lookup – 4 Different Ideas

I quite often get request to summarized my ideas about Removing bookmark lookup on this blog post. Bookmark lookup or key lookup are bad for any query as they force query engine to lookpup corresponding row in the table or index as it does not find required data from just reading the data. Here is every idea I have on removing a bookmark key lookup, in one list.

Here are list of my four post written on the same subject.

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2

SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 3

SQL SERVER – Removing Key Lookup – Seek Predicate – Predicate – An Interesting Observation Related to Datatypes

Let me know your opinion about the same and I am interested to here what you have to add to this subject.

How to Spot and Verify a Bookmark Key Lookup Fix

Before you fix anything, find it. Turn on the actual execution plan and look for a Key Lookup operator next to a Nested Loops join. On a table without a clustered index, the same idea shows up as a RID Lookup. Hover over it and check the Output List. Those are the columns your index did not have.

Keep in mind that a lookup is not always evil. For a handful of rows it costs very little. It becomes a problem when the query touches thousands of rows and does one lookup for each of them.

The usual fix is a covering index, often by adding the missing columns with INCLUDE. A common mistake is to keep adding columns for every query until the index is almost a second copy of the table. Every extra column makes inserts and updates a little slower, so cover the queries that really matter. Sometimes the cheaper fix is simply to stop selecting columns nobody uses.

To verify, run the query with SET STATISTICS IO ON before and after the change and compare logical reads. The lookup should be gone from the plan too.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.