I saw and answered a question today at ASK about the DMV sys.dm_db_index_usage_stats, a really useful DMV that shows you the usage of indexes in a database since the statistics were last reset (this happens when SQL Server is restarted, or the database is attached/detached or brought online).
The question asked what the data in the column system_scans actually meant. To better understand this, we have to understand what the usage stats are. This is my take on the matter (please get in touch with your views/comments).
The usage statistics for an index keep track of how often an index has been accessed, be it from a user through queries or through the system itself for internal use. The statistics are updated when one or more of the following occur:
- an index is used to satisfy a query
- an index is updated when data has been changed/deleted/inserted
- an index has been rebuilt/reorganised
- the statistics for an index have been recalculated
Note that the list above can be split into two categories. User actions (the first two) and system actions (the second two). These categories are also separated in the index usage statistics. This allows you to see at a glance, who (users/system) is responsible for the most index usage, and also what usage you are seeing.
The original question posted at ASK was wanting to know what the system_scan column data really meant. This counter keeps track of how often the index has been scanned (entirely or partially) by SQL Server internally. This type of operation would be for index rebuilds/reorgs or statistics updates. The system runs off and scans the index to collect the information it needs. This may have been triggered by a user requesting a statistics refresh, but the real work is still classed as being done by SQL Server internally.
If a user were to write a query that scanned an index, then the column user_scans would be incremented by one as it was a request by the user for data from the index. Equally, if an index seek is performed, the counter user_seeks would be incremented.
As a rule of thumb, if you have low user reads (user_scans+user_seeks) and high system values or high user updates, then an index may be a good candidate for being dropped.
But remember, the usage statistics are only as valid as your system uptime. First, a freshly started system will have low values across the board. Secondly, a system that has been running for over a year may have skewed values just through the long uptime. An index may have had high usage 6 months ago, but is now behaving totally differently after a further 6 months of use.