Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identifying Key and RID Lookup Issues and How to Resolve


By:   |   Read Comments (7)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

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.

Solution

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]
);
GO
CREATE NONCLUSTERED INDEX x
ON dbo.CustomerAddress(CustomerID, AddressID);
GO

(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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, November 08, 2017 - 8:59:03 AM - Hugo Back To Top

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


Wednesday, November 08, 2017 - 7:32:22 AM - Greg Robidoux Back To Top

Hi Hugo,

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

http://www.sentryone.com/plan-explorer

Thanks
-Greg


Wednesday, November 08, 2017 - 6:35:51 AM - Hugo Back To Top

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

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

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

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

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


Learn more about SQL Server tools