By: Aaron Bertrand | Comments (7) | Related: > Performance Tuning
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.
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:
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:
So now that we know what the problem is, your options at this point are to:
- alter the existing non-clustered index, adding the ModifiedDate as either a key or INCLUDE column;
- drop the ModifiedDate column from the query;
- consider a different indexing strategy for this table altogether; or,
- 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
- Download the free SQL Sentry Plan Explorer.
- Watch for Key or RID lookups in your plans.
- Identify opportunities for better performance by adding columns to indexes or removing unnecessary columns from queries.
- Review the following tips and other resources:
About the author
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, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
