Sql Server: Bookmark Lookups, a Query Performance Killer

If a non clustered index is created on expected WHERE clause and JOIN columns of a query but still to satisfy the query and to get values for column which are not included in non-clustered index, 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.

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

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.

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 best solution. For this we will modify our non-clustered index IX_StandardCos, by using include keyword as follow.

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 to optimize our query, but in real word identifying columns which should we 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 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 “Ouptpu List” and click on browse button to open popup window. From here you can easily copy name of columns to add in INCOLUDE 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 and there is no need to include this column in INCLUDE list.