What causes `system_scans` in sys.dm_db_index_usage_stats to count up?

As usual BOL only states the obvious here.

I have a few non clustered indexes in a database that have zero user_scans, user_seeks and user_lookups. These same indexes have high number of system_scans and user_updates.
Taking an educated guess here, but would the system_scans be from something like auto updating of statistics?

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?

asked Aug 03 '10 at 12:27 AM in Default

Nick Kavadias
466 ● 3 ● 3 ● 7

(comments are locked)

1 answer:

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