question

BradleySQL avatar image
BradleySQL asked

What is a RID lookup?

I have decided to start trying to learn more about performance tuning so I decided to take a look at a query that had a high count on the execution plan. When I put that query into SSMS and look at the estimated execution plan I am seeing an index seek but also with an RID Lookup. The RID lookup has a high cost. What exactly is an RID Lookup? I have heard of a bookmark lookup but not RID.
execution-plan
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Well don't I feel stupid. I should have looked this up. Thanks @trad and @kev riley.
0 Likes 0 ·
No worries - that's what this site is for!
0 Likes 0 ·
Tim avatar image
Tim answered
A RID Lookup is a lookup into a heap table using a Row ID. The Row ID is included in a non-clustered index in order to find the rest of a table's data in the heap table. Since a heap table is a table without a clustered index and is sorted unordered a Row ID is required for the correlation. You should really look to add a clustered index to this table. Is this an in house product or a database to a third party vendor application? I have ran into this numerous times with third party products. I have yet to come into a situation where I could not add a clustered index.
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

10K - Woot!
0 Likes 0 ·
Thanks @KenJ, your vote pushed me over the 10k mark. I am now the infamous 5 digits.
0 Likes 0 ·
Congrats @TRAD,impressive work to get your 10k. Thanks for visiting and helping out.
0 Likes 0 ·
Congrats on the 10K.
0 Likes 0 ·
Well done on 10K
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
You say you are familiar with bookmark lookups - well the RID lookup is actually the 'new' name for the same thing. It was introduced in SQL2005 SP2 - check this [blog article by Gail Shaw][1], where she explains the difference. [1]: http://sqlinthewild.co.za/index.php/2009/01/27/a-bookmark-lookup-by-any-other-name/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
I can't add anything to what Kev and TRAD have already said. But if you're just getting started with execution plans, I can recommend a good (free) book. [Go here to download it.][1] And yes, it's mine. [1]: http://www.sqlservercentral.com/articles/books/65831/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

and an excellent book it is!
0 Likes 0 ·
ambujsaxena avatar image
ambujsaxena answered
bookmark lookup or RID lookup showing in the Execuatuon plan when you select Columns not included on your index if table has clustered index, it is called **bookmark lookup** (or key lookup); if the table does not have clustered index, but a non-clustered index, it is called **RID lookup.** There is something exciting in SQL Server called **'Covering Indexes'** which can save you in such situation.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.