What is a key lookup (or RID lookup) in an execution plan?
Quick Answer
A key lookup happens when a non-clustered index seek finds the rows but doesn't contain all the columns the query needs, so SQL Server does an extra lookup into the clustered index (or a RID lookup into a heap) — once per matching row. A few are fine; thousands make the seek slower than a scan.
A key lookup is when a non-clustered index seek gets you to the right rows but is missing a column the query wants, so for each of those rows SQL Server has to jump back into the clustered index to fetch the rest. It's a nested loop in the plan. A handful of them is fine, but if the seek returns thousands of rows, that's thousands of lookups and the query would've been faster with a scan. The fix is to make the index cover the query by INCLUDE-ing the missing columns.
- In the plan it shows as a Key Lookup (Clustered) or RID Lookup (Heap) operator joined to the index seek via a nested loop.
- Cost scales with the number of rows the seek returns — selective filter, few lookups, no problem; broad filter, many lookups, big problem.
- Fix: add the missing columns to the index (in the key if they're filtered/sorted, in
INCLUDEif only selected) to make it covering. - Sometimes the optimiser gives up on the index entirely and scans because the lookups would cost more — a “tipping point”.