InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 19:52:30 GMT20http://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspx[quote][b]Grizzly Bear (1/24/2011)[/b][hr]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 ?[/quote]You don't. Only way to even get an idea is to look at the execution plans, but that's far from easy.Mon, 24 Jan 2011 11:20:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspxThanks CraigI see people have already discussed this. Thanks for pointing this out to me.:-)Mon, 24 Jan 2011 11:12:17 GMTGrizzly Bearhttp://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspxI 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 ?Mon, 24 Jan 2011 11:09:20 GMTGrizzly Bearhttp://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspxOh 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 ?Mon, 24 Jan 2011 11:06:15 GMTGrizzly Bearhttp://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspxThere was a longer discussion a while back here:[url]http://www.sqlservercentral.com/Forums/Topic840059-360-1.aspx#bm840576[/url]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.Mon, 24 Jan 2011 11:05:29 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspxIt's not a lookup [b]from [/b]that index. It's a lookup [b]to [/b]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)Mon, 24 Jan 2011 11:03:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspxUmmm 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 [b]user_lookups[i][/i][/b] 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. Mon, 24 Jan 2011 11:00:00 GMTGrizzly Bearhttp://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspxIt 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.)Mon, 24 Jan 2011 10:53:51 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspxI understand what she says or so I think.[quote][b]A lookup occurs when a nonclustered index was used to locate rows for a query[/b], 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[/quote]Precisely why I asked the question -- so what does it mean for a Clustered Index. Am I missing something in her article.ThanksMon, 24 Jan 2011 10:34:30 GMTGrizzly Bearhttp://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspxIf you check Gail's explanation [url=http://www.sqlservercentral.com/articles/Indexing/68563/]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.Mon, 24 Jan 2011 10:27:40 GMTs_osborne2http://www.sqlservercentral.com/Forums/Topic1052529-391-1.aspxHi AllWhen I queried dm_db_index_usage_stats, I get user_lookups for a Clustered Index Here are the numbers [b] seeks lookups[/b] 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.ThanksMon, 24 Jan 2011 10:21:32 GMTGrizzly Bear