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

Why are user_lookups > 0 on Clustered Index? Expand / Collapse
Author
Message
Posted Tuesday, December 29, 2009 2:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:27 PM
Points: 100, Visits: 360
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
Post #840059
Posted Wednesday, December 30, 2009 1:40 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: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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 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 #840221
Posted Wednesday, December 30, 2009 1:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:27 PM
Points: 100, Visits: 360
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
Post #840564
Posted Wednesday, December 30, 2009 1:48 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #840573
Posted Wednesday, December 30, 2009 1:57 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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 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 #840576
Posted Wednesday, December 30, 2009 2:09 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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 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 #840579
Posted Wednesday, December 30, 2009 3:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:27 PM
Points: 100, Visits: 360
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.
Post #840602
Posted Thursday, December 31, 2009 12:47 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: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
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 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 #840700
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse