WEEK 01 - KEY LOOKUP
The index was useful. But incomplete.
A scheduled stored procedure. 194,000 executions. Each one doing 2,835 logical reads.
After one INCLUDE column added to the index, the same query dropped to 10 logical reads.
THE SYMPTOM
High CPU. No single catastrophic query. Just a small inefficiency repeated 194,000 times.
THE EXECUTION PLAN
The plan had three nodes. Index Seek → Nested Loops → Key Lookup.
The Key Lookup was executing 920 times per stored procedure call.
Each one a round-trip back to the clustered index.
THE PATTERN
When you see Index Seek → Nested Loops → Key Lookup, stop and check.
Plan node | Meaning |
|---|---|
Index Seek | The index to rebuild |
Nested Loops | Output List → columns to add to INCLUDE |
Key Lookup | Node cost % = optimization potential |
THE ROOT CAUSE
The index covered (CompanyID, MovementID). The query also needed TransactionCode. Not in the index. Not in the INCLUDE list. Just missing.
920 lookups per execution. 194,000 executions. All to fetch one column that could have been right there.
THE FIX
CREATE NONCLUSTERED INDEX IX_SalesTransaction_Company_Movement
ON dbo.SalesTransaction
(
CompanyID,
MovementID
)
INCLUDE
(
TransactionCode
)
WITH (DROP_EXISTING = ON);The only change: adding TransactionCode to the INCLUDE clause. The index key stayed the same. SQL Server no longer needed to visit the clustered index to fetch that column.
THE NUMBERS
Metric | Value |
|---|---|
Logical reads before | 2,835 |
Logical reads after | 10 |
Reduction | 99.6% |
The query was executed 194,000 times. A small per-execution cost became real CPU pressure.
UNDER THE HOOD
A nonclustered index is a sorted copy of a column subset.
Every leaf node carries the clustering key.
When a query needs a column not in the index, SQL Server uses that key to jump back to the clustered index, once per row, inside a Nested Loop.
That jump is the Key Lookup.
No clustered index? The lookup becomes a RID Lookup. Same problem, same fix.
WATCH OUT
Do not add INCLUDE columns blindly. Every included column increases index size and write overhead.
Before changing an index, check: execution frequency, logical reads before/after, read/write ratio, overlapping indexes, deployment impact.
Same key columns on multiple indexes? Consolidate — one index, same key, union of all useful INCLUDE columns.
THE LESSON
The index was not wrong. It was incomplete. One missing column caused 920 Key Lookups per execution. Added to INCLUDE. Done.
Pier Giuseppe Mandosi
SQL Server Performance Diaries