Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Bookmark Lookups Expand / Collapse
Author
Message
Posted Monday, May 23, 2005 2:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 14, 2007 1:40 PM
Points: 42, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/RDyess/bookmarklookups.asp


Post #184628
Posted Tuesday, May 31, 2005 12:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 17, 2012 2:14 PM
Points: 50, Visits: 25
good informative article on optimizing query performance.
Post #186107
Posted Wednesday, June 01, 2005 4:09 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512
Thanks, nice article.  
Post #186814
Posted Tuesday, September 20, 2005 2:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 09, 2005 10:22 AM
Points: 102, Visits: 1
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.

Post #221464
Posted Tuesday, May 16, 2006 9:45 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 2:15 PM
Points: 27, Visits: 145

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

 

Post #280412
Posted Wednesday, May 17, 2006 9:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 12:30 PM
Points: 24, Visits: 1

Good article! very informative. Thank you.

Explanation was very clear. neat examples.

 

Post #280744
Posted Tuesday, May 30, 2006 7:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 19, 2009 6:00 AM
Points: 4, Visits: 20
This is an excellent article. Beware of the bookmark lookups they use more resources than expected.
Post #283554
Posted Friday, July 27, 2007 2:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 08, 2010 12:20 PM
Points: 105, Visits: 6

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.  

Post #385704
Posted Friday, July 27, 2007 6:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:25 AM
Points: 846, Visits: 466
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
Post #385734
Posted Friday, July 27, 2007 8:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 02, 2008 2:47 PM
Points: 1, Visits: 13

Great article, I learned a lot.

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

Post #385830
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse