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 12»»

Lookups on Clustered Indexes Expand / Collapse
Author
Message
Posted Monday, January 24, 2011 10:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 5, 2013 9:07 AM
Points: 46, Visits: 175
Hi All

When I queried dm_db_index_usage_stats, I get user_lookups for a Clustered Index

Here are the numbers

seeks lookups
14723 62569

Question is what do the user_lookups on Clustered Index mean to me? I always thought the lookups are only found for Non-Clustered Index.

Thanks
Post #1052529
Posted Monday, January 24, 2011 10:27 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:17 AM
Points: 702, Visits: 2,177
If you check Gail's explanation here then this should help you. She explains lookups very well.

The other 2 articles in the series are also very useful and worth a read when you get time.





MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1052533
Posted Monday, January 24, 2011 10:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 5, 2013 9:07 AM
Points: 46, Visits: 175
I understand what she says or so I think.

A lookup occurs when a nonclustered index was used to locate rows for a query, but the nonclustered index did not contain all of the columns required for the query. To fetch the remaining columns, a lookup is done to the clustered index.

A lookup is equivalent to a single row clustered index seek. Lookups are always done one row at a time. For this reason, they are very expensive operations, especially when lots of rows are involved


Precisely why I asked the question -- so what does it mean for a Clustered Index. Am I missing something in her article.

Thanks
Post #1052544
Posted Monday, January 24, 2011 10:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:04 AM
Points: 1,030, Visits: 2,795
It means that all the data was reuested in the query was not contained in the nonclustered index. So it used the clustering key contained in the non-clustered index to look up the rest of the columns from the clustered index (which is the table.)

Gethyn Ellis

gethynellis.com
Post #1052567
Posted Monday, January 24, 2011 11:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 5, 2013 9:07 AM
Points: 46, Visits: 175
Ummm I don't think I have made myselves clear here.

All that you guys are pointing out to me is for a NON CLUSTERED index and I understand that, but then why Do I see the numbers I have posted above for a CLUSTERED Index?

When I query dm_db_index_usage_stats, I see a the number for user_lookups for a Clustered Index. I always thought that number should be 0 for a Clustered Index considering that Clustered Indexes don't have to go through "lookups". Or am I wrong in my assumption.

Post #1052572
Posted Monday, January 24, 2011 11:03 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 @ 3:30 PM
Points: 42,848, Visits: 35,977
It's not a lookup from that index. It's a lookup to that index. Typically it will be 0 for nonclustered indexes (because key lookups aren't done to nonclustered indexes) and non-zero for the cluster (because key lookup are done to the clustered index)


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 #1052576
Posted Monday, January 24, 2011 11:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 6,251, Visits: 7,411
There was a longer discussion a while back here:
http://www.sqlservercentral.com/Forums/Topic840059-360-1.aspx#bm840576

Nonclustered indexes lookup off the clustered. Non-clusters shouldn't have any user_lookups, only the clustered will when the NC's have to go use the clustered for a lookup.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1052579
Posted Monday, January 24, 2011 11:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 5, 2013 9:07 AM
Points: 46, Visits: 175
Oh wait a minute Gethyn -- are you trying to say that even though Lookups happen for NON CLUSTERED index -- it uses the CLUSTERED INDEX as a key for Lookups, the number for the Clustered Index in the dm_db_index_usage_stats -- user_lookups column increases by 1 ?


Post #1052580
Posted Monday, January 24, 2011 11:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 5, 2013 9:07 AM
Points: 46, Visits: 175
I see, I understand what you are saying now.

Then this begs the next question -- how do I know which NON_CLUSTERED index used this CLUSTERED index for "lookups" and how many times each did ?


Post #1052586
Posted Monday, January 24, 2011 11:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 5, 2013 9:07 AM
Points: 46, Visits: 175
Thanks Craig

I see people have already discussed this. Thanks for pointing this out to me.
Post #1052589
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse