SQL Server RID Lookup Operator

Hi Geeks,

Non-clustered indexes can exist both on heaps as well as objects with clustered indexes and it is possible to have a key lookup on a heap.It is reflected in query plans as SQL Server RID Lookup; following statement will produce same execution plan.

USE [AdventureWorks2012]

SELECT *
FROM dbo.DatabaseLog
WHERE DatabaseLog.DatabaseLogID = 10

RIDLookup

As can be seen, instead of key lookup operator query plan displays RID Lookup operator because heaps don’t have clustering keys as clustered index do and instead they have row identifiers a.k.a RID. A RID is a row locator that includes information like database file, page, slot numbers and helps to identify specific rows quickly. Point to be noted here is, every row in a nonclustered index that is created on a heap contains RID of the corresponding record.

IndSeekRIDToolTip

To return the results for the statement used in our example, SQL Server query optimizer performs an index seek on the primary key PK_DatabaseLog_DatabaseLogID (see image above). As highlighted Output list of index seek operator contains ‘Bmk1000’ which tells us, all the data columns needed to be returned by the statement weren’t part of the key and hence query optimizer performed a RID Lookup.

RIDLookupToolTip

This an additional I/O overhead as two different operations are performed instead of a single one. This time ‘Bmk1000’ used again and in Seek Predicate section which tells us a bookmark lookup was used in query plan.

If result set is small, then this isn’t anything to be worried about but if RID lookup returns many records we should start looking at re-writing the query or to define appropriate clustered or covering index.

Both key lookup and RID lookups are accompanied by a nested loop join which we are going to cover in future.

Happy learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBookFollow me on TwitterFollow me on FaceBook

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

One Comment on “SQL Server RID Lookup Operator”

Leave a Reply

Your email address will not be published. Required fields are marked *