<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Lookups on Clustered Indexes
11 posts, Page 1 of 2
1
2
»»
Lookups on Clustered Indexes
Rate Topic
Display Mode
Topic Options
Author
Message
Grizzly Bear
Grizzly Bear
Posted Monday, January 24, 2011 10:21 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 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
s_osborne2
s_osborne2
Posted Monday, January 24, 2011 10:27 AM
SSChasing Mays
Group: General Forum Members
Last Login: Yesterday @ 9:18 AM
Points: 608,
Visits: 1,854
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.
Follow me on Twitter:
@WazzTheBadger
LinkedIn Profile:
Simon Osborne
Post #1052533
Grizzly Bear
Grizzly Bear
Posted Monday, January 24, 2011 10:34 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 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
GRE (Gethyn Ellis)
GRE (Gethyn Ellis)
Posted Monday, January 24, 2011 10:53 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 9:55 AM
Points: 1,024,
Visits: 2,768
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
Grizzly Bear
Grizzly Bear
Posted Monday, January 24, 2011 11:00 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 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
GilaMonster
GilaMonster
Posted Monday, January 24, 2011 11:03 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640,
Visits: 29,895
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
Evil Kraig F
Evil Kraig F
Posted Monday, January 24, 2011 11:05 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 5,658,
Visits: 6,099
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
Grizzly Bear
Grizzly Bear
Posted Monday, January 24, 2011 11:06 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 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
Grizzly Bear
Grizzly Bear
Posted Monday, January 24, 2011 11:09 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 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
Grizzly Bear
Grizzly Bear
Posted Monday, January 24, 2011 11:12 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 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 »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
