December 24, 2013 at 3:38 am
Hi all, and merry xmas!!!
I just wanted some clarification on the user_lookups column in the index usage stats DMV. I've been looking through hoping to identify some indexes that potentially require some rework. I have found a number of indexes where the user lookup count is extremely high, but the seeks/scans and updates are very low, zero even.
Does mean that, an index has a seek operation performed on it (Index A user seeks increases by 1)...but the query isn't satisfied by the index so a key lookup is performed on another index (Index B user lookups increases by 1) - so in this case, potentially we have indexes that are purely being used for key lookups?
Appreciate its a lot more in-depth than my example, but am I on the right lines?
Many thanks
D
'Only he who wanders finds new paths'
December 24, 2013 at 5:39 am
You will only find lookups > 0 on clustered indexes. It is the number of key lookups that are done *to* that clustered index after a seek operation (or sometimes a scan) is done on a non-covering nonclustered 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
December 24, 2013 at 5:43 am
Thanks Gail that was pretty much what I thought!
Merry xmas 🙂
D
'Only he who wanders finds new paths'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply