SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bookmark Lookups


Bookmark Lookups

Author
Message
Randy_Dyess
Randy_Dyess
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/RDyess/bookmarklookups.asp



imty
imty
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 38
good informative article on optimizing query performance.
einman33
einman33
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 512
Thanks, nice article.
David Poole-249495
David Poole-249495
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 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.
WatchOut4Keith
WatchOut4Keith
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 192

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


Ravi Prashanth Lobo-275382
Ravi Prashanth Lobo-275382
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 1

Good article! very informative. Thank you.

Explanation was very clear. neat examples.


Erol Kucukarslan
Erol Kucukarslan
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 20
This is an excellent article. Beware of the bookmark lookups they use more resources than expected.
Michele Adams
Michele Adams
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 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.


Bryant McClellan
Bryant McClellan
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 542
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
David Tiss
David Tiss
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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?


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search