Bookmark Lookups

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/RDyess/bookmarklookups.asp

  • good informative article on optimizing query performance.

  • Thanks, nice article.  

  • OK adding extra fields into an index eliminates the bookmark lookup but surely they become unavoidable because we can't index everything! Well we can but the performance penalty for maintaining those indexes will be huge, not to mention the additional storage requirements.

  • I'm surprised at the lack of response to this article and votes for it over the last year. This is pretty important for optimization. I think the best advice from this article is: "If the column is not needed then don't include it." I'm guilty of the "just in case I need it" method myself. I will certainly be limiting the columns to only the necessary ones in the future after reading this article.

    The same concern that Journeyman posted also worries me. I worked with a database that was indexed to death and the indexes made things worse in some cases. I think I would save moving to a non-clustered index or a covering index as a last resort. (That is if the table has an index to begin with.)

     

  • Good article! very informative. Thank you.

    Explanation was very clear. neat examples.

     

  • This is an excellent article. Beware of the bookmark lookups they use more resources than expected.

  • It'd be nice if there were a mention of optimizing bookmark lookups instead of just elimination.  A narrow index often will produce rows much faster than even a clustered index--especially when seeking on wide tables.  Likewise covering too many columns slows index performance. 

    An alternative is to put narrow non-clustered indexes on separate spindles.  This helps immensely, especially when the results must have too many columns to cover--just put join and predicate columns in the non-clustered index, and be sure it's on separate spindles than the data.  

  • Great article, both on recognizing what a bookmark is and how to handle it. However, in your text you state that there are 4 methods to counter but only list 3. What is the other one?

    >>

    Resolving bookmark lookups

    Once you discover the columns responsible for a bookmark lookup, you will need to consider one of four methods that are available to resolve the bookmark lookup.

    1. Create a covering index

    2. Remove the offending column

    3. Convert a non-clustered index into a clustered index

    <<

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

  • Great article, I learned a lot.

    I'm wondering if the included column feature in SQL Server 2005 will also help with bookmark lookups? 

  • Funny that I just saw this article. We JUST tackled this exact situation in a large SS database last week.  Good article !  I wish i would have had this info two weeks ago.

  • With SQL Server 2005 you also have the opportunity to Include additional columns in your index.  This is in BOL at:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/d198648d-fea5-416d-9f30-f9d4aebbf4ec.htm

    "An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations."

  • Amazing article ....... On similar lines please find another link :: http://blogs.msdn.com/craigfr/archive/2006/06/30/652639.aspx

  • Excellent and very useful article...

  • David Tiss (7/27/2007)


    Great article, I learned a lot.

    I'm wondering if the included column feature in SQL Server 2005 will also help with bookmark lookups?

    INCLUDE columns absolutely help with lookup issues.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply