Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clustered Index - sys.dm_db_index_usage_stats


Clustered Index - sys.dm_db_index_usage_stats

Author
Message
mike.bishop-593913
mike.bishop-593913
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 151
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44366
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mike.bishop-593913
mike.bishop-593913
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 151
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.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6320 Visits: 2048
Hopefully your developers are not using select *... If that is not the case you can't do much Wink


* Noel
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