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

615 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.9k 39 92 168

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 ♦♦
66.1k 48 63 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 44 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

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.

more ▼

answered Mar 11 at 07:12 PM

avatar image

1 1

(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: 45192 times

Last Updated: Mar 11 at 07:12 PM

Copyright 2017 Redgate Software. Privacy Policy