SQL Server: Bookmark Lookups, a Query Performance Killer

Introduction

If a non clustered index is created on an expected WHERE clause and JOIN columns of a query, still to satisfy the query and to get values for column which are not included in non-clustered index, the optimizer refers to data pages. This trip to data pages to satisfy a query, although you have a valid non-clustered index, is called bookmark lookup.

Background

I remember my early days of DBA career, when bookmark lookups were a big performance problem for me. I was really worried that performance was not up to the mark although I had proper non-clustered indexes.

Using the Code

Let’s try to understand this phenomenon through an example.

USE AdventureWorks
GO

CREATE NONCLUSTERED  INDEX [IX_StandardCost]
ON [Production].[Product] ( [StandardCost] ASC )
ON  [PRIMARY]
GO
SELECT  ProductID, Name, ProductNumber, StandardCost
FROM    Production.Product
WHERE   StandardCost = 1265.6195

Table 'Product'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0

In our example, bookmark lookup occurred because two columns in select “Name, ProductNumber” are not included in non-clustered index.

What about ProductID, we will discuss it later in this post.

The problem can be resolved if non-clustered can satisfy all required columns. We never want to use these extra columns in index key to avoid wide key ambiguities. Covering index is the best solution. For this, we will modify our non-clustered index IX_StandardCos, by using the include keyword as follows:

CREATE NONCLUSTERED  INDEX [IX_StandardCost]
ON [Production].[Product] ( [StandardCost] ASC )
    INCLUDE ( ProductID, Name, ProductNumber )
WITH (DROP_EXISTING = ON)
ON  [PRIMARY]
GO

SELECT  ProductID, Name, ProductNumber, StandardCost
FROM    Production.Product
WHERE   StandardCost = 1265.6195

Table 'Product'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0

Covering index solved our problem and we were successful in optimizing our query, but in the real world identifying columns which we should include in COVERING INDEX is not so simple. Optimizer may need columns used in any part of query like SELECT clause, WHERE clause, JOINs, FUNCTIONS, GROUP BY, etc.

There is a solution for every problem. I like to use the following methods to identify columns that must be included in non-clustered index.

Method 1

Move your mouse pointer to “Key Lookup” and note down columns mentioned in “Output List” section.

Method 2

Click on “Key Lookup”, and press F4 to open properties window. Move to “Output List” and click on browse button to open popup window. From here, you can easily copy name of columns to add in INCLUDE list of non-clustered index.

Note

Column list contains “Name, ProductNumber” but product_id is not included. As product_id is clustered index column and every non-clustered index already contains clustered index column at leaf page as pointer, there is no need to include this column in INCLUDE list.