Identifying Key and RID Lookup Issues and How to Resolve

By:   |   Comments (7)   |   Related: > Performance Tuning


Formerly known simply as "bookmark lookups", Key or RID lookups can represent performance issues that don't always bubble to the top of your tuning efforts, especially when your data is still relatively small. These lookups occur when an index does not satisfy the query (non-covered query) and therefore additional data needs to be retrieved from the clustered index or the heap. Non-covered queries can be a problem because, for every row in the index, the additional column(s) must then be fetched; this can have a significant impact on large data sets and impact overall performance.

A Key lookup occurs when the table has a clustered index and a RID lookup occurs when the table does not have a clustered index, otherwise known as a heap. They can, of course, be a warning sign of underlying issues that may not really have an impact until your data grows.

When I see a RID lookup, I always step back and question why the table doesn't have a clustered index (a RID lookup only occurs on a heap). And when I see either type of lookup, I wonder why the index selected does not cover the columns required by the query. I'll leave the missing clustered index issue for another day, and focus on non-covered queries.


In this tip we will look at how to identify Key and RID lookups and some options to eliminate them.

Let's take the following simple heap (table without a clustered index):

CREATE TABLE [dbo].[CustomerAddress]
[CustomerID] [int],
[AddressID] [int],
[ModifiedDate] [datetime]
ON dbo.CustomerAddress(CustomerID, AddressID);

(Now, this table is not very realistic, as it has neither a primary key nor a clustered index. While I am sure tables like this do exist in many environments, this is a simplistic example for illustrative purposes.)

Let's see what our execution plan looks like in SQL Server Management Studio when we run this simple query:

SELECT CustomerID, AddressID, ModifiedDate
FROM dbo.CustomerAddress
WHERE CustomerID = 29485;

We can see below that we have a RID lookup, which makes sense since we do not have a clustered index on this table. The rest of the data is a little cryptic to understand why this RID lookup is occurring. If you have read a lot of execution plans you might be able to figure this out, but for those that are not looking at execution plans all day long it may not be obvious.

how to identify Key and RID lookups and some options to eliminate them

In a previous tip (More intuitive tool for reading SQL Server execution plans), I gave a high-level overview of SQL Sentry Plan Explorer, a free tool offered by my employer, SQL Sentry, Inc. I want to show how this tool simplifies identifying these issues. So let's see the same plan in SQL Sentry Plan Explorer, where it highlights the costliest operators and shows tabular data identifying, among other things, the number of Key and RID lookups for each statement:

sql sentry plan explorer

And then moving to the Query Columns tab, we can see that the column that is not being covered (ModifiedDate) is automatically highlighted for you, making it very easy to see why this operation requires a RID lookup:

the column is already highlighted, making it easy to see why this operation requires a rid lookup

So now that we know what the problem is, your options at this point are to:

  1. alter the existing non-clustered index, adding the ModifiedDate as either a key or INCLUDE column;
  2. drop the ModifiedDate column from the query;
  3. consider a different indexing strategy for this table altogether; or,
  4. leave the lookup in place and hope it never becomes a performance sore point.

There may be a discovery process here; for example, you may need to talk to the business users of this query to see if and why they need the ModifiedDate column, and if they do, you may also need to test performance differences in both read and write workloads against this table with the changed index in place. Never just change an index and walk away.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, November 8, 2017 - 8:59:03 AM - Hugo Back To Top (69434)

Thanks Greg! Just gave it a shot and looks like a great tool.

Wednesday, November 8, 2017 - 7:32:22 AM - Greg Robidoux Back To Top (69430)

Hi Hugo,

Plan Explorer is still free.  Here is the link to the page on the SentryOne website.


Wednesday, November 8, 2017 - 6:35:51 AM - Hugo Back To Top (69428)

To bad SQL Sentry is no longer free. A licence for $2495,- makes it a tool for specilist and I sure hope they dont need this article anymore.


Tuesday, March 18, 2014 - 10:17:33 AM - jeff Back To Top (29798)

Thanks for the tip, Aaron. I rarely, if ever, gave thought to fields in the select list unless they were also factors in a join, 'order by' or 'where' group.

Tuesday, April 19, 2011 - 8:23:56 AM - Jeremy Kadlec Back To Top (13633)

Ahmad and Aaron,

This is just one way to approach the problem.  It is good to know the options available.

Thank you,
Jeremy Kadlec

Tuesday, April 19, 2011 - 7:53:15 AM - Aaron Bertrand Back To Top (13632)

It's a free tip, about a free product, and you can use the advice without the free product as well (it's just a little more work).

What exactly is the complaint?

In fact, several other readers have asked for more tips like this. Can't please everyone.

Tuesday, April 19, 2011 - 2:01:32 AM - Ahmad Back To Top (13631)

Good simple article, but please dont be such product specific... Thanks

get free sql tips
agree to terms