What is a Bookmark Lookup in Sql Server?

I'm in the process of trying to optimize a query that looks up historical data. I'm using the query analyzer to lookup the Execution Plan and have found that the majority of my query cost is on something called a "Bookmark Lookup". I've never seen this node in an execution plan before and don't know what it means.

Is this a good thing or a bad thing in a query?

From stackoverflow

• A bookmark lookup is the process of finding the actual data in the SQL table, based on an entry found in a non-clustered index.

  When you search for a value in a non-clustered index, and your query needs more fields than are part of the index leaf node (all the index fields, plus any possible INCLUDE columns), then SQL Server needs to go retrieve the actual data page(s) - that's what's called a bookmark lookup.

  In some cases, that's really the only way to go - only if your query would require just one more field (not a whole bunch of 'em), it might be a good idea to INCLUDE that field in the non-clustered index. In that case, the leaf-level node of the non-clustered index would contain all fields needed to satisfy your query (a "covering" index), and thus a bookmark lookup wouldn't be necessary anymore.

  Marc

  : Agreed. If a large percentage of the table is returned it can be better to just have a table scan, but if statistics are poor you may get a plan that does the bookmark lookups instead. There is a pretty decent free ebook on execution plans at redgate - http://www.red-gate.com/specials/Grant.htm

  marc_s : Yup, the Red Gate site was lots of great SQL Server stuff available - lots of it for free, too!

• It's a NESTED LOOP which joins a non-clustered index with the table itself on a row pointer.

  Happens for the queries like this:

  SELECT  col1
  FROM    table
  WHERE   col2 BETWEEN 1 AND 10
  

  , if you have an index on col2.

  The index on col2 contains pointers to the indexed rows.

  So, in order to retrieve the value of col1, the engine needs to scan the index on col2 for the key values from 1 to 10, and for each index leaf, refer to the table itself using the pointer contained in the leaf, to find out the value of col1.

  This article points out that a Bookmark Lookup is SQL Server 2000's term, which is replaced by NESTED LOOP's between the index and the table in SQL Server 2005 and above

  marc_s : CLUSTERED KEY LOOKUP is the bookmark lookup, AFAIK.

  : It may be a bit confusing to refer to it as a NESTED LOOP, it isn't really related to the NESTED LOOP join type right? I guess you are just speaking conceptually?

  Quassnoi : Each record in a table has a physical pointer that can be used to access this record. A non-clustered index is in fact another table. When you make a SELECT * FROM table WHERE col1 BETWEEN a AND b, in fact there is a hidden join: SELECT * FROM col1_index JOIN table ON table.rowid = col1_index WHERE col1_index.col1 BETWEEN a AND b. This join can be NESTED LOOP and even HASH (when two indexes are joined together).

• From MSDN regarding Bookmark Lookups:

  The Bookmark Lookup operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. The Argument column contains the bookmark label used to look up the row in the table or clustered index. The Argument column also contains the name of the table or clustered index in which the row is looked up. If the WITH PREFETCH clause appears in the Argument column, the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the table or clustered index.