When SQL Key Lookups Become Costly

What is a Key Lookup?

A Key Lookup happens when SQL Server uses a nonclustered index to find rows, but that index does not contain all the columns required by the query.

SQL Server then accesses the clustered index separately to retrieve the missing columns.

Example

SELECT CustomerId, CustomerName, Email
FROM Customers
WHERE CustomerId = 100;

If the nonclustered index contains only CustomerId, SQL Server performs a Key Lookup to retrieve CustomerName and Email.

When does it become expensive?

It becomes expensive when:

• Many rows require lookups.

• The lookup runs once for every matching row.

• The query frequently reads missing columns.

• Many additional data pages must be accessed.

For a few rows, a Key Lookup is normally acceptable.

Possible solution

Create a covering index using included columns:

CREATE INDEX IX_Customers_CustomerId
ON Customers(CustomerId)
INCLUDE (CustomerName, Email);

Key point

A Key Lookup is not always a problem. Optimize it only when it processes many rows or consumes a significant part of the query cost. Avoid adding too many included columns because larger indexes require more storage and slow down data modifications.