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.
more ▼

asked May 05, 2011 at 12:38 PM in Default

BradleySQL gravatar image

555 4 8 9

Well don't I feel stupid. I should have looked this up. Thanks @trad and @kev riley.
May 05, 2011 at 02:15 PM BradleySQL
No worries - that's what this site is for!
May 06, 2011 at 01:06 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.
more ▼

answered May 05, 2011 at 12:41 PM

Tim gravatar image

36.4k 38 41 139

10K - Woot!
May 05, 2011 at 01:10 PM KenJ
Thanks @KenJ, your vote pushed me over the 10k mark. I am now the infamous 5 digits.
May 05, 2011 at 01:15 PM Tim
Congrats @TRAD,impressive work to get your 10k. Thanks for visiting and helping out.
May 05, 2011 at 02:58 PM Fatherjack ♦♦
Congrats on the 10K.
May 05, 2011 at 03:36 PM TimothyAWiseman
Well done on 10K
May 05, 2011 at 04:05 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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/
more ▼

answered May 05, 2011 at 01:59 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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/
more ▼

answered May 05, 2011 at 04:05 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

and an excellent book it is!
May 06, 2011 at 01:05 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 05, 2011 at 12:38 PM

Seen: 21651 times

Last Updated: Jan 14 at 12:36 PM