The nonclustered index finds the key, then visits the table for another column. RID lookups identify that second visit on a heap. A clustered table uses a different row locator for the same missing data.
Find What the Index Didn't Contain
I inspect the selected columns when a lookup dominates a plan. The first index has found qualifying rows. The lookup retrieves columns that weren't available in that access path.
A heap has no clustered index. Its nonclustered row locator identifies a physical row location. The execution plan shows a RID Lookup when that location supplies the missing values.
A clustered table's nonclustered indexes use the clustered key as the locator. The plan shows a Key Lookup when it retrieves missing columns. Both lookups commonly appear under nested loops.
The row locator isn't the business filter itself. An index can search CustomerCode while locating the row through another identifier. Keep the searched key and lookup locator distinct.
A lookup isn't automatically a defect. Fetching a few wider values can be appropriate. The problem appears when many qualifying rows require repeated visits.
Build Matching Heap and Clustered Samples
Use a disposable database for the following tables. The two designs receive the same generated sample rows. Their input population isn't an observed application measurement.
The heap has a unique nonclustered identifier index but no clustered index. That unique index doesn't turn the table into a clustered structure. Its data still lives as a heap.
The second table clusters on ItemId. Both have a nonclustered index on GroupId. Neither group index includes the payload at this stage.
The payload starts short and has a larger declared limit. That allows a later update to increase its stored size. The update helps illustrate forwarded-record behavior on the heap.
Keep the same requested columns when comparing plans. Selecting the identifier alone would change the coverage requirement. The example deliberately requests the missing payload.
CREATE TABLE dbo.HeapLookupDemo
(ItemId int NOT NULL, GroupId int NOT NULL, Payload varchar(1000) NOT NULL);
CREATE UNIQUE NONCLUSTERED INDEX UX_HeapLookupDemo_Item ON dbo.HeapLookupDemo(ItemId);
CREATE INDEX IX_HeapLookupDemo_Group ON dbo.HeapLookupDemo(GroupId);
CREATE TABLE dbo.ClusteredLookupDemo
(ItemId int NOT NULL PRIMARY KEY CLUSTERED, GroupId int NOT NULL, Payload varchar(1000) NOT NULL);
CREATE INDEX IX_ClusteredLookupDemo_Group ON dbo.ClusteredLookupDemo(GroupId);
INSERT dbo.HeapLookupDemo(ItemId, GroupId, Payload)
SELECT n, n % 100, REPLICATE('x', 50)
FROM (SELECT TOP (5000) ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id) AS n
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS numbers;
INSERT dbo.ClusteredLookupDemo SELECT ItemId, GroupId, Payload FROM dbo.HeapLookupDemo;Read the Properties of RID Lookups
Enable the actual execution plan in SSMS before running the next queries. Inspect the index access and the lookup. The optimizer still chooses the plan for the actual sample.
A broad selected group can make a scan cheaper than many lookups. That is a reasonable alternative to inspect. Don't claim the sample must show one exact plan shape.
The RID lookup's locator refers to the heap row's physical location. The key lookup uses the clustered key. Inspect the properties to see the locator and requested output columns.
Read actual executions and rows beside each lookup. A small result per execution can hide a large repeated total. The outer branch determines how frequently the inner lookup is needed.
RID lookups and key lookups solve the same missing-column problem through different storage structures. That difference becomes particularly useful when the heap has forwarded records. First establish the ordinary lookup behavior.
SET STATISTICS IO ON;
SELECT ItemId, Payload FROM dbo.HeapLookupDemo WHERE GroupId = 10;
SELECT ItemId, Payload FROM dbo.ClusteredLookupDemo WHERE GroupId = 10;
SET STATISTICS IO OFF;
Inspect Forwarded Records Behind RID Lookups
A growing heap row sometimes no longer fits on its original page. SQL Server can move it and leave a forwarding pointer. An access through the original locator then follows that pointer.
The nonclustered locator doesn't need every index entry rewritten for that move. The extra hop is the tradeoff. Repeated forwarding adds work to heap access.
The sample update below increases payload width in the heap. It doesn't guarantee a particular number of forwarded records on every server. Inspect the measured physical statistics afterward.
DETAILED inspection can read substantial data on a large object. Use it on the disposable sample or during an appropriate review window. Filter to the heap's in-row allocation data.
The returned forwarded_record_count reports the heap's observed condition. It isn't a per-query cost measurement. Compare the application query's reads separately.
UPDATE dbo.HeapLookupDemo SET Payload = REPLICATE('x', 700);
SELECT index_id, page_count, record_count, forwarded_record_count, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.HeapLookupDemo'), 0, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = N'IN_ROW_DATA';Make the Comparison Fair Again
The heap payload now contains more data than the clustered sample. Update the second table too before comparing query reads. Otherwise the requested payload widths differ.
That clustered update has its own page-management effects. Clustered tables can split pages when rows grow. They don't use heap forwarding pointers for that mechanism.
Don't blame every extra read on forwarding alone. Wider values, allocation changes and cache state also influence the result. Use the physical statistics and actual access path together.
I keep the row values equivalent during a storage comparison. A query returning different widths isn't measuring one isolated design choice. The data matters as much as the table label.
Repeat the selected group query with the same projection. Record the actual operator shape and reads after the change. No result or improvement is asserted before your execution.
UPDATE dbo.ClusteredLookupDemo SET Payload = REPLICATE('x', 700);
SET STATISTICS IO ON;
SELECT ItemId, Payload FROM dbo.HeapLookupDemo WHERE GroupId = 10;
SELECT ItemId, Payload FROM dbo.ClusteredLookupDemo WHERE GroupId = 10;
SET STATISTICS IO OFF;Cover RID Lookups When the Workload Justifies It
Adding Payload as an included column lets the group index supply the requested data. Adding ItemId explicitly covers the heap's identifier output too. The clustered index already carries its locator where needed.
The following rebuild changes the two group indexes. The data projection remains the same. Inspect whether the resulting plan removes the lookups.
Coverage costs storage and write maintenance. A large payload copied into another index isn't free. Test important workload queries rather than including every column everywhere.
What frequency and qualifying row population make this lookup expensive? Answer with actual plans and workload observations. One isolated lookup icon doesn't justify a wide index.
RID lookups deserve the same coverage review as key lookups. Their heap-specific forwarding risk adds another storage investigation. Keep those two reasons separate.
CREATE INDEX IX_HeapLookupDemo_Group ON dbo.HeapLookupDemo(GroupId)
INCLUDE(ItemId, Payload) WITH (DROP_EXISTING = ON);
CREATE INDEX IX_ClusteredLookupDemo_Group ON dbo.ClusteredLookupDemo(GroupId)
INCLUDE(Payload) WITH (DROP_EXISTING = ON);
SELECT ItemId, Payload FROM dbo.HeapLookupDemo WHERE GroupId = 10;
SELECT ItemId, Payload FROM dbo.ClusteredLookupDemo WHERE GroupId = 10;Choose the Storage Fix from the Evidence
Rebuilding a heap can remove forwarded records, but later growth can create them again. A clustered design changes that storage behavior. Both are broader decisions than covering one query.
I compare the important writes and reads before recommending that change. I also review existing locator width and index maintenance cost. A tidy plan picture isn't the full workload.
Use the lookup properties to identify missing columns. Use physical statistics to identify heap forwarding. Then test the smallest appropriate change against the actual query.
The lookup isn't wandering without a purpose. It is fetching data the first index didn't carry. Give that extra trip a measured reason before paying to remove it.
Related reading on this blog: SQL SERVER Performance Tuning: Catching Key Lookup in Action and SQL SERVER Heaps: Understanding Their Benefits and Limitations.
A lookup is not an unexplained detour, it is a row visit for data missing from the first index.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
