Post reply

Bookmark Lookups

  • May 23, 2005 at 2:35 pm

    #113797

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

  • May 31, 2005 at 12:00 am

    #561807

    good informative article on optimizing query performance.

  • June 1, 2005 at 4:09 pm

    #562380

    Thanks, nice article.  

  • September 20, 2005 at 2:41 am

    #591238

    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.

  • May 16, 2006 at 9:45 am

    #638191

    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.)

     

  • May 17, 2006 at 9:12 am

    #638457

    Good article! very informative. Thank you.

    Explanation was very clear. neat examples.

     

  • May 30, 2006 at 7:11 am

    #640667

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

  • July 27, 2007 at 2:36 am

    #722801

    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.  

  • July 27, 2007 at 6:06 am

    #722826

    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

  • July 27, 2007 at 8:54 am

    #722907

    Great article, I learned a lot.

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

  • July 27, 2007 at 2:10 pm

    #723051

    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.

  • August 1, 2007 at 9:09 am

    #723908

    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."

  • May 13, 2008 at 5:58 am

    #814372

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

  • July 18, 2008 at 2:14 am

    #844849

    Excellent and very useful article...

  • July 18, 2008 at 7:07 am

    #844998

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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