February 6, 2007 at 7:24 am
I have one problem in a query.
At first, query leads index scan and i created a index and then it shows huge bookmark lookup in big table.
Why bookmark lookup happens ? Is it good or bad ?
How can we remove suck lookups ?
Please provide me some solutions.
AKP
February 6, 2007 at 9:36 am
Bookmark is better than a scan, however:-
There's no "golden" fix to queries, it all depends on the query its self and the underlying data - if we fixed it for you chances are you'd only run into another probelms as the data changed - best you read up and get an understanding yourself.
Generally a bookmark lookup can be replaced with a covered index, however this might not be the best solution and / or you may not be able to define a covered index ( for instance the query may include a text column ) When the result set is wide you often have to use a bookmark or clustered index seek/scan ( and if there are joins then it becomes more complex ) as I say it "Just depends."
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 6, 2007 at 11:42 pm
A bookmark lookup occurs when SQL uses a nonclustered index to locate the rows required for a query, but the index does not contain all the columns needed by the query. SQL then has to do a lookup to the clustered index/heap to retrieve the rest of the data.
As for are they good or bad, it depends. Sometimes a NC index seek, followed by a bookmark lookup is faster than a scan of the cluster/heal, sometimes it's slower. Depends on the number of rows returned and the size of the table. The optimiser usually makes the best choice.
As for getting rid of them... It's not easy to get rid of bookmark lookups. Generally it depends on the query, the table, the indexes and the volumn of data what to do.
Only retrieve the data that you need in the query. No select *
You may be able to create a covering index, but then make sure that doesn't degrade insert/update/delete performance.
Retrieve fewer rows (if possible) the fewer rows returned, the cheaper the bookmark lookups are.
In adtion to Colin's list of resources, I'd recomend http://www.sqlskills.com Kimberly has a lot of resources available about indexing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2007 at 1:32 am
Yes I should have included Kimberly in that list, better still if she or Kalen are running any courses/seminars get booked on to them double quick.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 7, 2007 at 2:23 am
You might also find Randy Dyess' article on Bookmark Lookups useful:
http://www.sqlservercentral.com/columnists/RDyess/bookmarklookups.asp
rgds iwg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply