This object gives statistics on how an index has been used to resolve queries. Most importantly it tells you the number of times a query was used to find a single row (user_seeks), a range of values, or to resolve a non-unique query (user_scans ), if it has been used to resolve a bookmark lookup (user_lookups) and how many changes to the index (user_updates. Note that sys.dm_db_index_operational_stats will give the details of how it has been modified.)

It returns all indexes (including heaps and the clustered index) from the entire server for each index that has been used, though you will usually only want to use it for one database since you will have to look up the name of the index in sys.indexes. There will not be a row in sys.dm_db_index_usage_stats unless the index has been used since creation or since SQL Server has been restarted.

Type: view

Data: accumulating, refreshed when server is restarted or (perhaps obviously) when the index is dropped and recreated. Statistics live on when the index is rebuild, reorganized, and even when it is disabled and rebuilt.

Columns:

• database_id – primary key of the database
• object_id – the object_id of the table the index belongs to
• index_id – the primary key of the index
• user_seeks – the number of times the index has been used in a user query in a seek operation (one specific row)
• user_scans – the number of times the index has been used by scanning the leaf pages of the index for data
• user_lookups – for clustered indexes only, this is the number of times the index has been used in a “bookmark lookup” to fetch the full row. This is because non-clustered indexes use the clustered indexes key as the pointer to the base row.
• user_updates – The number of times the index has been modified due to a change in the table’s data.
• last_user_seek – The date and time of the last user seek operation
• last_user_scan – The date and time of the last user scan operation
• last_user_lookup – The date and time of the last user lookup operation
• last_user_update – The date and time of the last user update operation
• system_seeks, system_scans, system_lookups, system_updates, last_system_seek, last_system_scan, last_system_lookup, last_system_update – Same as the user query columns, but records when the index is used for a system operation, such as automatic statistics operations.

This is one of the most interesting views that I often use in performance tuning. It gives you something that no previous version of SQL Server did (at least in an easy to discover manner for the “average” dba, that I know of): The ability to tell when indexes are NOT being used. It is easy to see when an index is being used by a query by simply looking at the plan. But now, using this dynamic management view, you can see over time what indexes are used, not used, and probably more importantly, updated many many times and never being used.

Examples:

Create table, do a few queries in tempdb.  Table and full code will be in final book.

–returns all indexes for a database and their stats.
–Rows with no usage since the last restart will be null

select object_schema_name(indexes.object_id) + ‘.’ + object_name(indexes.object_id) as objectName,
         indexes.name, case when is_unique = 1 then ‘UNIQUE ‘ else ” end + indexes.type_desc,
         ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates
from sys.indexes
               left outer join sys.dm_db_index_usage_stats ddius
                        on indexes.object_id = ddius.object_id
                             and indexes.index_id = ddius.index_id
                             and ddius.database_id = db_id()
order by ddius.user_seeks + ddius.user_scans + ddius.user_lookups desc