I think I may have confused some people by talking about bookmark lookups. I’ll attempt to clarify things.
The operator that I’m talking about is the one that fetches extra columns from the clustered index when the nonclustered index that’s used to retrieve the rows doesn’t have all of the columns required.
In SQL 2000, that operator appeared in the execution plan as a bookmark lookup and it appeared as follows:
In SQL 2005, the name was changed, and the bookmark lookup no longer appeared in the execution plan. In it’s place appeared a clustered index seek, joined back to the original index seek by a nested loop join. It appeared as follows (the highlighted operator is the ‘bookmark lookup’)
This change made it harder to see what was going on as clustered index seeks are usually considered ‘good’. The trick to see if it is actually a ‘bookmark lookup’ is to look at the objects involved. When the nonclustered index seek and the clustered index seek are both on the same table, then it’s a ‘bookmark lookup’
I don’t recall what this appeared as when the base table was a heap, not a cluster.
In SQL 2005 SP2, the name of the operator was changed again, now appearing as a key lookup (when the base table has a clustered index) or a RID lookup (when the base table is a heap). It now looks like this:
The thing to note is that it’s not the version of the server that’s important. The format of the XML for the exec plan has not changed since SQL 2005 RTM (I can and have created a .sqlplan file from SQL 2008 and opened that file in SQL 2005’s management studio).
It’s the version of management studio that affects how the execution plans are displayed. If the server is SQL 2005 SP3, but the client tools are still RTM, the bookmark lookup will appear as a clustered index seek. Another reason to patch the client as well as the server
I hope that clears up some of the confusion around the naming. So, in future, what should I refer to this as? A bookmark lookup? A Key lookup?
I’ve been trying, not entirely successfully, to use Key Lookup when referring to… that thing… that’s not a bookmark lookup. I presented on Saturday and vacillated between the two terms so much I’m sure I confused people.
I realised that I’ve been doing the same thing, both when I present, on blog posts and on forum replies.
Didn’t help at all that last time I presented, I showed an execution plan and started talking about the key lookups, then realised (after several people in the audience looked confused) that my demo machine was SQL 2005 RTM and the exec plan clearly showed a clustered index seek.
I think Bookmark Lookup is still applicable because it encompasses the Key Lookup and the RID Lookup under one umbrella term. The both do similar things and have an impact on performance and potential deadlocking in the database.
I still call it a bookmark lookup, but I think you need to explain what MS calls it in each version for your audience to understand it.
ooops I have a feeling this blog post may have been a result of my confusion 🙂 thanks for the clearing it up for me the other day. I think bookmark look-up is accepted
Great post. I vote for bookmark look-up. 🙂
I vote for Key Lookup.
Not only for the deprecation of SQL Server 2000, but because that’s what all Lookups really are. They use a key to re-read a table and the matching values stored by keys in a worktable in TempDB.
bottom line question if this is 97% of a cost in a query can it be better tuned?
Sure, tuning this involves widening the index so that it covers the query.
thanks for your post
if the bookmark found the row
why nested loop used then ?
nested loop confused me 🙁
It’s just the different ways that SQL represents the operation. A nested loop shows the way that SQL joined together the data from the nonclustered index and the values that it looked up from the cluster.