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 – 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 (https://blog.sqlauthority.com)
2 Comments. Leave new
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?
Hi Pinal,
After removing the key lookup I can see nested loops join and index seek out of one of those nonclustered index is showing as 100% and the query retrieval time is also not reduced it is the same time as around 3 minutes.