As usual [BOL] only states the obvious here.
I have a few non clustered indexes in a database that have zero
Is my guess correct? any idea on a simple way of testing out the theory? What other processes cause system usage stats to count up on user tables/indexes?: http://technet.microsoft.com/en-us/library/ms188755.aspx
I understand the system scans/lookups columns are to do with statistics updates & index maintenance. I asked Brent Ozar when he was presenting over in Germany about a month ago and he confirmed this for me.I asked him about this with regard to identifying unused indexes on a system. He said that you only have to be concerned with User Lookups and User Scans, the system ones are really only system side operations. So an index that only has "system" activity has not been used by queries since the index usage statistics were last reset.
answered Aug 03 '10 at 01:25 AM