Why are user_lookups > 0 on Clustered Index?

Question

Why are user_lookups > 0 on Clustered Index?

digitalox

SSCommitted

Points: 1830
More actions
December 29, 2009 at 2:05 pm

#215002

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 SSCommitted Points: 1830 More actions · Answer 2

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 SSC Guru Points: 260824 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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 SSCommitted Points: 1830 More actions · Answer 6

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 ( ), it is kinda long and there's probably something I'm missing in it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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