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


Why are user_lookups > 0 on Clustered Index?


Why are user_lookups > 0 on Clustered Index?

Author
Message
digitalox
digitalox
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 458
Hello,

I'm querying sys.dm_db_index_usage_stats and seeing values > 0 for the column user_lookups on some clustered indexes. Its my understanding that since the clustered index is the table, there are no lookups. What am I missing here?

Regards,
Scott
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87833 Visits: 45274
That shows the lookup done to the clustered index. So whenever a NC index is not covering and there's a key lookup, that's a lookup to the clustered index.

As far as I'm aware, user_lookups will only be > 0 for clustered indexes (key lookups) and heaps (rid lookups)

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


digitalox
digitalox
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 458
Thanks for your reply. I'm still a bit confused can I rephrase my question here?

This confusion started with this script I got off the net (maybe here) for analyzing indexes, which recommends drops/creates etc. based on usage. One of the things that confused me about it and lead to this question was its logic for when it might be time to re-evaluate a clustered index.

snippet from script:
CASE WHEN user_lookups > user_seeks AND type_desc IN ('CLUSTERED', 'HEAP', 'UNIQUE CLUSTERED') THEN 'REALIGN'

This confused me because if the user_lookups represents the bookmarks by a NC without sufficient cover, then how can that column have any bearing on the effectiveness of the clustered index?

Thanks,
Scott
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23637 Visits: 9730
It doesn't have any bearing on the effectiveness of the clustered index. I'd have to know what the purpose of the index query you have is, and what it means by "realign", to know what it means there, but it certainly has no bearing on the effectiveness of the clustered index.

- 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87833 Visits: 45274
digitalox (12/30/2009)
This confused me because if the user_lookups represents the bookmarks by a NC without sufficient cover, then how can that column have any bearing on the effectiveness of the clustered index?


It doesn't. I'd question wherever you got that script from. All it indicates is how frequently queries do lookups to fetch missing columns. If I saw lots and lots of lookups, I might evaluate the effectiveness on my nonclustered indexes, see if any need widening or making into covering indexes, but not the cluster.

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87833 Visits: 45274
digitalox (12/30/2009)
snippet from script:
CASE WHEN user_lookups > user_seeks AND type_desc IN ('CLUSTERED', 'HEAP', 'UNIQUE CLUSTERED') THEN 'REALIGN'


I would really question the accuracy of that script. The only valid values for type_desc are HEAP, CLUSTERED, NONCLUSTERED, XML and SPATIAL. 'UNIQUE CLUSTERED' is not a value that type_desc can be, so there's no point in it being in the IN.

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


digitalox
digitalox
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 458
Thanks very much for the replies, at the very least the usage of the column is clear.
Seems to be it would be more useful if the user_lookup values were populated in each instance of the NC indexes instead so that you could identify the offending ones rather than just have them lumped in the clustered/heap.

I'm going to look back over the script tonight ( [url=http://blogs.digineer.com/blogs/jasons/archive/2009/04/29/analyze-this-analyze-your-indexes-part-6.aspx][/url] ), it is kinda long and there's probably something I'm missing in it.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87833 Visits: 45274
digitalox (12/30/2009)
Seems to be it would be more useful if the user_lookup values were populated in each instance of the NC indexes instead so that you could identify the offending ones rather than just have them lumped in the clustered/heap.


But this is the index_usage_stats dmv. It shows how the indexes are being used. The cluster is the one used for the lookups, not the nonclusters. Hence it would have to show up against the cluster

Technically, the nonclusters don't do the lookups. The optimiser adds a lookup step to a plan if it needs columns that the nonclustered index(es) didn't provide

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


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