| title | sys.dm_db_index_usage_stats (Transact-SQL) | Microsoft Docs | ||||
|---|---|---|---|---|---|
| ms.custom | |||||
| ms.date | 03/20/2017 | ||||
| ms.prod | sql | ||||
| ms.prod_service | database-engine, sql-database, sql-data-warehouse, pdw | ||||
| ms.reviewer | |||||
| ms.technology | system-objects | ||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | d06a001f-0f72-4679-bc2f-66fff7958b86 | ||||
| author | stevestein | ||||
| ms.author | sstein | ||||
| manager | craigg | ||||
| monikerRange | >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDEtsql-appliesto-ss2008-all-md]
Returns counts of different types of index operations and the time each type of operation was last performed.
In [!INCLUDEssSDSfull], dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn’t belong to the connected tenant is filtered out.
Note
sys.dm_db_index_usage_stats does not return information about memory-optimized indexes. For information about memory-optimized index use, see sys.dm_db_xtp_index_stats (Transact-SQL).
Note
To call this view from [!INCLUDEssSDWfull] or [!INCLUDEssPDW], use sys.dm_pdw_nodes_db_index_usage_stats.
| 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 that did not use 'seek' predicate. |
| user_lookups | bigint | Number of bookmark lookups by user queries. |
| user_updates | bigint | Number of updates by user queries. This includes Insert, Delete, and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count increments by 1 |
| 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. |
| pdw_node_id | int | Applies to: [!INCLUDEssSDWfull], [!INCLUDEssPDW] The identifier for the node that this distribution is on. |
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 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 [!INCLUDEssNoVersion] (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.
During upgrade to [!INCLUDEssKilimanjaro], [!INCLUDEssSQL11], or [!INCLUDEssSQL14], entries in sys.dm_db_index_usage_stats are removed. Beginning with [!INCLUDEssSQL15], entries are retained as they were prior to [!INCLUDEssKilimanjaro].
On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On [!INCLUDEssSDS_md], requires the VIEW DATABASE STATE permission in the database.
Index Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)
sys.indexes (Transact-SQL)
sys.objects (Transact-SQL)
Monitor and Tune for Performance