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

Clustered Index - sys.dm_db_index_usage_stats Expand / Collapse
Author
Message
Posted Tuesday, June 17, 2008 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 4:44 AM
Points: 48, Visits: 148
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
Post #518204
Posted Tuesday, June 17, 2008 7:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #518241
Posted Tuesday, June 17, 2008 8:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 4:44 AM
Points: 48, Visits: 148
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.
Post #518279
Posted Tuesday, June 17, 2008 8:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #518308
Posted Tuesday, June 17, 2008 3:09 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
Hopefully your developers are not using select *... If that is not the case you can't do much ;)




* Noel
Post #518602
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse