sys.dm_db_index_usage_stats

Returns counts of different types of index operations and the time each type of operation was last performed.

Column name Data type Description

database_id

smallint

ID of the database on which the table or view is defined.

object_id

int

ID of the table or view on which the index is defined

index_id

int

ID of the index.

user_seeks

bigint

Number of seeks by user queries.

user_scans

bigint

Number of scans by user queries.

user_lookups

bigint

Number of bookmark lookups by user queries.

user_updates

bigint

Number of updates by user queries.

last_user_seek

datetime

Time of last user seek

last_user_scan

datetime

Time of last user scan.

last_user_lookup

datetime

Time of last user lookup.

last_user_update

datetime

Time of last user update.

system_seeks

bigint

Number of seeks by system queries.

system_scans

bigint

Number of scans by system queries.

system_lookups

bigint

Number of lookups by system queries.

system_updates

bigint

Number of updates by system queries.

last_system_seek

datetime

Time of last system seek.

last_system_scan

datetime

Time of last system scan.

last_system_lookup

datetime

Time of last system lookup.

last_system_update

datetime

Time of last system update.

Remarks

Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly all by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero.

Permissions

Requires VIEW SERVER STATE permission. For more information, see Dynamic Management Views and Functions.

See Also

Reference

Dynamic Management Views and Functions
Index-Related Dynamic Management Views and Functions
sys.dm_db_index_physical_stats
sys.dm_db_index_operational_stats
sys.indexes (Transact-SQL)
sys.objects (Transact-SQL)

Other Resources

General Index Design Guidelines
Monitoring and Tuning for Performance

Help and Information

Getting SQL Server 2005 Assistance