Key Lookups: The Hidden Performance Killer?

What a Key Lookup Is, in One Paragraph

A nonclustered index stores its key columns, any INCLUDE columns and the clustering key. If a query needs another column, SQL Server finds the matching rows in the nonclustered index and then fetches each row from the clustered index with a separate seek: the Key Lookup (on a heap: RID Lookup). In the plan it sits under a Nested Loops join and runs once per row. The basics, with an animated diagram, are in Index Seek vs. Index Scan vs. Key Lookup vs. Table Scan. This article is about the cost.

Finding 1: Each Lookup Costs About One Index Depth in Reads

Every lookup is a full seek from the root of the clustered index down to the leaf. With an index depth of 3, that is 3 logical reads per row, plus the few pages of the nonclustered index. The measurement matches this almost exactly: 515 rows cost 1,589 reads, 19,877 rows cost 60,920 reads. The full scan of the clustered index costs 60,707 reads, no matter how many rows qualify.

So the read break-even point is simple arithmetic: rows × index depth = pages of the table. Here that is about 20,000 rows, or 1% of the table. With wider rows (fewer rows per page) the break-even point moves down, with narrow rows it moves up. Percentages from blog posts ("lookups are fine up to 5%") are meaningless without this context.

Same query, forced plans, warm cache. Top: logical reads grow linearly with each looked-up row, the scan stays flat at 60,707. Bottom: CPU time tells a different story. The dotted vertical line marks where the optimizer switched from the lookup plan to a scan.

Finding 2: The Optimizer Switches Close to the Read Break-Even Point

With literal values and fresh statistics, the optimizer kept the lookup plan up to 21,897 rows and switched to a clustered index scan at 23,900 rows, at about 1.1% of the table. That is just above the read break-even point of about 20,000 rows. The well-known "tipping point" is not a fixed percentage. It is the point where the estimated cost of thousands of random single-page reads exceeds one sequential scan, and it follows the page count of the table.

Finding 3: In a Warm Cache, the Optimizer Gives Up on Lookups Too Early

The bottom half of the chart is the surprise. With all pages in memory, the lookup plan used less CPU than the scan up to about 4% of the table. At 2% (39,921 rows), the lookup plan needed 60 ms, the scan the optimizer chose needed 112 ms, although the lookup plan read twice as many pages. A logical read of a page that is already in memory is cheap. Touching every row of 60,707 pages is not.

The optimizer's cost model assumes that random reads go to disk. It does not know whether your table is in memory. For a hot OLTP table that is fully cached, a moderate lookup count is rarely the bottleneck, even beyond the tipping point. Reads in the plan are not the same as time.

Finding 4: On a Cold Cache, Data Locality Decides

Lookups get expensive when the pages are not in memory. I cleared the buffer pool before each run (DBCC DROPCLEANBUFFERS, test instance only) and compared the elapsed time of the forced plans. In addition to the random CustomerID lookups, I looked up the same number of rows through the OrderDate index. Because rows were inserted in date order, consecutive lookups hit neighboring rows in the clustered index.

Random lookups lost to the scan as soon as the read break-even point was reached. With 20,000 random customers, the query touches more than a quarter of all pages of the table, one at a time, and read-ahead cannot help much. Lookups on neighboring rows stayed below the scan until well beyond that point, because the same few clustered pages served many lookups. The container runs on fast local SSD storage; on a SAN with higher latency per random read, the gap between random lookups and the scan is larger.

This is the real answer to "is a key lookup a problem?": it depends on how many rows, how far apart they are, and whether they are in memory. None of these three is visible in the operator icon.

Finding 5: The Real Killer Is a Wrong Estimate

So far the optimizer knew how many rows to expect. Now the same query in a stored procedure with parameters:

CREATE OR ALTER PROCEDURE dbo.GetOrders @from int, @to int
AS
SELECT CustomerID, Amount, ShipCity
FROM   dbo.Orders
WHERE  CustomerID BETWEEN @from AND @to;

The first call with @from = 1, @to = 10 compiles a plan for about 100 rows: seek plus lookup. The next call with 1, 20000 reuses that plan for 199,739 rows.

Ten times the reads. In a warm cache the elapsed time was only 25% worse, which is exactly why these plans survive in production: they look acceptable in the test environment, where everything is cached and runs alone. On a cold cache, with random rows and a SAN underneath, the same plan does 200,000 random single-page reads. That is the scenario behind most "key lookup killed my server" stories. The lookup itself is innocent; it is the lookup multiplied by an estimate that is off by a factor of 2,000.

Finding 6: INCLUDE Works, and It Has a Price

The textbook fix makes the index covering:

CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID)
INCLUDE (Amount, ShipCity)
WITH (DROP_EXISTING = ON);

The read side is spectacular, for every range size and every parameter value, because the plan no longer depends on the estimate:

The write side is the part that missing-index suggestions never show:

The index grew by a factor of 2.5. Every update of an included column now also changes the nonclustered index, which tripled the log volume of that update. Inserts produced 85% more log. Updates of columns that are not included are unaffected. In an availability group, every one of those log bytes also goes over the network to every replica (FCI or availability groups).

For one hot query with two narrow, rarely updated columns, that is an excellent trade. For ten "missing index" suggestions, each including half the table, it is how you end up with a table whose indexes are larger than the table itself and an insert rate that halved over two years.

How to Find the Lookups That Matter

A key lookup in a plan is not a finding. A key lookup that executes very often per call, or one whose estimated executions are far below the actual ones, is. The following query searches the plan cache for lookup operators and sorts by logical reads:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (20)
       DB_NAME(st.dbid)                                   AS database_name,
       OBJECT_NAME(st.objectid, st.dbid)                  AS object_name,
       qs.execution_count,
       qs.total_logical_reads / qs.execution_count        AS avg_logical_reads,
       lk.value('(IndexScan/Object/@Table)[1]', 'sysname') AS lookup_table,
       lk.value('@EstimateRebinds', 'float') + 1          AS est_lookup_executions
FROM   sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY qp.query_plan.nodes('//RelOp[IndexScan/@Lookup = 1]') AS x(lk)
ORDER BY qs.total_logical_reads DESC;

In the test it found the sniffed procedure with an average of 318,679 reads per execution and an estimate of 100 lookups, against 199,739 actual ones. That gap is the signal. Compare est_lookup_executions with the actual row counts from the execution plan or Query Store.

What to Do, in This Order

1. Check the row count first. A lookup on a handful of rows per execution is fine. Leave it.
2. Check the estimate. If estimated and actual lookups differ by orders of magnitude, fix the estimate, not the index: update statistics, use OPTION (RECOMPILE) for rare queries with very different parameter values, or OPTIMIZE FOR a representative value.
3. Trim the select list. SELECT * and "just in case" columns are the most common reason a lookup exists at all. Removing an unused column costs nothing.
4. INCLUDE narrow columns for hot queries. If the query runs often, the columns are narrow and rarely updated, make the index covering. Measure the index size before and after.
5. Consider a filtered index if the query always targets a small subset (WHERE Status = 'Open'). It can include columns at a fraction of the size.
6. Do not include wide or volatile columns. nvarchar(max), JSON payloads, counters and timestamps that change with every update make the write side pay for every read you save.
7. Do not apply missing-index suggestions unreviewed. They are generated per query, do not consider your existing indexes or the write cost, and like to include everything.

The Bottom Line

Key lookups are not a hidden performance killer. They are a predictable cost: about one index depth in reads per row, cheap in a warm cache, expensive for random rows on a cold one. The optimizer switches to a scan at roughly the read break-even point, which in my test was about 1% of the table, and in memory it often switches too early rather than too late. What turns a lookup into an incident is a plan built for 100 rows running for 200,000. Find those by comparing estimated and actual executions, fix the estimate where you can, and spend INCLUDE columns deliberately: in the test they cut reads by a factor of 70 to 700 and tripled the log volume of updates.

Related reading: Index Seek vs. Index Scan vs. Key Lookup vs. Table Scan, B-Tree Index Seek Explained and Index Optimization and Statistics Update.