Click here to Skip to main content
Click here to Skip to main content

SQL Server: Bookmark Lookups, a Query Performance Killer

, 1 Nov 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
You have place indexes but still queries are slow... are you facing bookmark lookup problem ???

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.
Follow on   Twitter   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 2 Nov 2012
Article Copyright 2012 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid