June 17, 2008 at 7:12 am
I'm hoping this is an easy one to answer but all comments are very welcome.
This post is driven from one of my tables that has 1889434 records with 6 indexes of which one is clustered. The results from sys.dm_db_index_usage_stats on the clusteredindex shows 377740 seeks, 60618 scans but 30099577 lookups. (stats over the last 4 weeks)
In the sys.dm_db_index_usage_stats DMV the field user_lookups is the total number of bookmark lookups. Correct?
Working that this is correct I just need a little clarity on why a bookmark lookup occurs on a clustered index. Given that the leaf level of the index is the actual data table (Correct?) why does a bookmark lookup possibly happen when the seek/search is already on the data row?
If the data fields being returned in a query are not included in the clustered index does a bookmark lookup also occur? Or maybe the bookmark lookups are when a nonclustered index then uses the clustered key to get the data.
Either way my question is why do bookmark lookups occur on a clustered index?
Thanks in advance for any and all replies
June 17, 2008 at 7:41 am
mike.bishop (6/17/2008)
In the sys.dm_db_index_usage_stats DMV the field user_lookups is the total number of bookmark lookups. Correct?
Correct
Either way my question is why do bookmark lookups occur on a clustered index?
They don't occur on the clustered index. They occur to the clustered index.
A lookup occurs when SQL has used one of the nonclustered indexes to find rows,but it needs columns that are not in that nonclustered index, so it does a lookup to the clustered index to find the missing columns.
Make more sense?
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
June 17, 2008 at 8:18 am
Thanks for the response.
Your reply made the penny drop and rather than view the clustered index as an index and therefore index seeks/scans if I think of it as an ordered table it all makes sense!
I'm guessing that to avoid so many bookmark lookups I should add the missing field(s) causing the bookmark lookup to either the nonclustered or clustered index or add the field to the nonclustered index as an included column.
June 17, 2008 at 8:36 am
You can add those columns to the include part of the non-clustered indexes, which will reduce your bookmark lookups, but keep in mind that it will also slow down insert/update/delete actions. If you add them, make sure the rest of the functions are still working acceptably.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 3:09 pm
Hopefully your developers are not using select *... If that is not the case you can't do much 😉
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply