Why are user_lookups > 0 on Clustered Index?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Why are user_lookups > 0 on Clustered...

Why are user_lookups > 0 on Clustered Index?

Rate Topic

Display Mode

Topic Options

Author

Message

digitalox

Posted Tuesday, December 29, 2009 2:05 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:41 PM
Points: 74, Visits: 279

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

GilaMonster

Posted Wednesday, December 30, 2009 1:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099, Visits: 30,392

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

digitalox

Posted Wednesday, December 30, 2009 1:23 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:41 PM
Points: 74, Visits: 279

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

GSquared

Posted Wednesday, December 30, 2009 1:48 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442, Visits: 9,572

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

GilaMonster

Posted Wednesday, December 30, 2009 1:57 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099, Visits: 30,392

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

GilaMonster

Posted Wednesday, December 30, 2009 2:09 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099, Visits: 30,392

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

digitalox

Posted Wednesday, December 30, 2009 3:13 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, February 21, 2013 4:41 PM
Points: 74, Visits: 279

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

GilaMonster

Posted Thursday, December 31, 2009 12:47 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099, Visits: 30,392

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 »

Permissions