Eliminate Bookmark Lookups in SQL Server: Index Strategies Explained

What is a bookmark lookup? When the optimizer chooses a non‑clustered index that does not contain all requested columns, it must perform a lookup to retrieve the missing columns from the base table. On a table with a clustered index this is a key lookup ; on a heap it is a RID lookup . The lookup adds extra logical reads of data pages in addition to the index pages.

Test table example

The sample table has a clustered primary key PK_UserID and a non‑clustered index IX_UserName on the UserName column.

Running the query

select UserName, Gender from dbo.UserInfo where UserName='userN600'

produces an execution plan with a bookmark (key) lookup, as shown in the following screenshot.

If the query is reduced to select UserName from dbo.UserInfo where UserName='userN600' the bookmark lookup disappears because the non‑clustered index now covers all requested columns.

How to eliminate bookmark lookups

Method 1 – Use a clustered index on the queried column

Because the leaf pages of a clustered index store the actual table rows, reading the indexed key column also returns all other columns without a separate lookup. Creating a clustered index on UserName would remove the bookmark lookup, but it requires dropping the existing primary key PK_UserID, which may affect foreign‑key relationships and other queries.

Method 2 – Create a covering (include) index

A covering index contains all columns needed by the query, so the optimizer never needs to access the base table. Adding the Gender column to the non‑clustered index IX_UserName via the INCLUDE clause solves the problem.

Example:

CREATE NONCLUSTERED INDEX IX_UserName_Cover ON dbo.UserInfo (UserName) INCLUDE (Gender);

Use INCLUDE when you want to avoid enlarging the index key, when the column type cannot be indexed directly (except text, ntext, image), or when the index already has the maximum number of key columns.

Method 3 – Use index intersection

Index intersection combines multiple indexes to satisfy a query. For a query like

select UserName, Gender from dbo.UserInfo where UserName='userN600' and Gender=1

you can create a non‑clustered index on Gender and then force the optimizer to use both indexes:

select Gender, UserName from dbo.UserInfo with (index(IX_Gender, IX_UserName)) where UserName='jins' and Gender=0;

This technique is useful when a single covering index would be too wide or when you want to keep separate indexes for different query patterns.