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

avatar image

595 8 10 13

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

avatar image

40.4k 39 85 166

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, where she explains the difference.

more ▼

answered May 05, 2011 at 01:59 PM

avatar image

Kev Riley ♦♦
64.9k 48 62 81

(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.

And yes, it's mine.

more ▼

answered May 05, 2011 at 04:05 PM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

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.

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: 41989 times

Last Updated: Jan 14, 2014 at 12:36 PM

Copyright 2016 Redgate Software. Privacy Policy