SQL SERVER – Four Posts on Removing the Bookmark Lookup – Key Lookup

Recently, I have observed that not many people have proper understanding of what is bookmark lookup or key lookup. Increasing numbers of the questions tells me that this is something that developers encounter every single day, but have no idea how to deal with. I have previously written three posts on this subject. All those who are looking for further information can check out the following three posts.

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 – Interesting Observation – Execution Plan and Results of Aggregate Concatenation Queries

In one of my recent classes, we had an in-depth conversation on what are the alternatives of creating covering indexes to remove the bookmark lookup. I really want to keep this question open to all of you and see what community thinks about the same. Is there any way other than creating a covering index or included index to remove this expensive keylookup?

Reference: Pinal Dave (http://blog.sqlauthority.com)

2 thoughts on “SQL SERVER – Four Posts on Removing the Bookmark Lookup – Key Lookup

  1. I’ve been looking at a query that one of our third-party applications uses to populate one of its forms. I’ve found that it does a “SELECT *” from a table, with a sub query against the same table. The estimated execution plan shows a Key Lookup at a cost of 99%.

    I considered adding an index that included all of the columns in the table– not sure if that was even a good idea but I was going to try it as a test. However, the table contains a number of ntext fields that cannot be included in the index. Kick blocked.

    Given that I cannot modify either the query itself as it is hard-coded, or the underlying table structures for the same reason– is there anything I can do to improve the performance of the query?

    Like

  2. Pingback: SQLAuthority News – Monthly Roundup of Best SQL Posts Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s