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


Lookups on Clustered Indexes


Lookups on Clustered Indexes

Author
Message
Grizzly Bear
Grizzly Bear
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 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
s_osborne2
s_osborne2
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 2292
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.



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Grizzly Bear
Grizzly Bear
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 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
Gethyn Ellis
Gethyn Ellis
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1801 Visits: 2890
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 Ellisgethynellis.com
Grizzly Bear
Grizzly Bear
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118654 Visits: 45547
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, 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


Evil Kraig F
Evil Kraig F
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11007 Visits: 7660
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
Grizzly Bear
Grizzly Bear
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 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 ?
Grizzly Bear
Grizzly Bear
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 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 ?
Grizzly Bear
Grizzly Bear
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 175
Thanks Craig

I see people have already discussed this. Thanks for pointing this out to me.:-)
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