Post reply

Lookups on Clustered Indexes

  • January 24, 2011 at 10:21 am

    #245735

    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

  • January 24, 2011 at 10:27 am

    #1277334

    If you check Gail's explanation here[/url] 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

  • January 24, 2011 at 10:34 am

    #1277345

    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

  • January 24, 2011 at 10:53 am

    #1277361

    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 Elliswww.gethynellis.com

  • January 24, 2011 at 11:00 am

    #1277366

    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.

  • January 24, 2011 at 11:03 am

    #1277370

    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

  • January 24, 2011 at 11:05 am

    #1277373

    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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 24, 2011 at 11:06 am

    #1277374

    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 ?

  • January 24, 2011 at 11:09 am

    #1277379

    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 ?

  • January 24, 2011 at 11:12 am

    #1277382

    Thanks Craig

    I see people have already discussed this. Thanks for pointing this out to me.:-)

  • January 24, 2011 at 11:20 am

    #1277385

    Grizzly Bear (1/24/2011)

    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 ?

    You don't.

    Only way to even get an idea is to look at the execution plans, but that's far from easy.

    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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply