sys.dm_db_index_usage_stats (Transact-SQL)sys.dm_db_index_usage_stats (Transact-SQL)

本主题适用于:yesSQL Server(从 2008 开始)yesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

返回 SQL ServerSQL Server 中不同类型索引操作的计数以及上次执行每种操作的时间。Returns counts of different types of index operations and the time each type of operation was last performed in SQL ServerSQL Server.

Azure SQL DatabaseAzure SQL Database 中,动态管理视图不能公开将影响数据库包含的信息,也不能公开有关用户可以访问的其他数据库的信息。In Azure SQL DatabaseAzure SQL Database, 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.

备注

sys.dm_db_index_usage_stats不返回有关内存优化索引的信息。sys.dm_db_index_usage_stats does not return information about memory-optimized indexes. 有关内存优化索引使用的信息,请参阅sys.dm_db_xtp_index_stats (Transact SQL ).For information about memory-optimized index use, see sys.dm_db_xtp_index_stats (Transact-SQL).

备注

若要从我们称之为 Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data Warehouse,使用名称sys.dm_pdw_nodes_db_index_usage_statsTo call this from Azure SQL 数据仓库Azure SQL Data Warehouse or 并行数据仓库Parallel Data Warehouse, use the name sys.dm_pdw_nodes_db_index_usage_stats.

列名Column name 数据类型Data type DescriptionDescription
database_iddatabase_id intsmallint 在其中定义表或视图的数据库的 ID。ID of the database on which the table or view is defined.
object_idobject_id intint 为其定义索引的表或视图的 ID。ID of the table or view on which the index is defined
index_idindex_id intint 索引的 ID。ID of the index.
user_seeksuser_seeks bigintbigint 通过用户查询执行的搜索次数。Number of seeks by user queries.
user_scansuser_scans bigintbigint 通过用户查询执行的扫描次数。Number of scans by user queries. 这表示未使用 seek 谓词的扫描。This represents scans that did not use 'seek' predicate.
user_lookupsuser_lookups bigintbigint 由用户查询执行的书签查找次数。Number of bookmark lookups by user queries.
user_updatesuser_updates bigintbigint 通过用户查询执行的更新次数。Number of updates by user queries. 这包括插入、 删除和更新表示的操作完成不受影响的实际行数。This includes Insert, Delete and Updates representing number of operations done not the actual rows affected. 例如,如果你删除在一个语句中的 1000年行,此计数将递增 1For example, if you delete 1000 rows in one statement, this count will increment by 1
last_user_seeklast_user_seek datetimedatetime 用户上次执行搜索的时间。Time of last user seek
last_user_scanlast_user_scan datetimedatetime 用户上次执行扫描的时间。Time of last user scan.
last_user_lookuplast_user_lookup datetimedatetime 用户上次执行查找的时间。Time of last user lookup.
last_user_updatelast_user_update datetimedatetime 用户上次执行更新的时间。Time of last user update.
system_seekssystem_seeks bigintbigint 通过系统查询执行的搜索次数。Number of seeks by system queries.
system_scanssystem_scans bigintbigint 通过系统查询执行的扫描次数。Number of scans by system queries.
system_lookupssystem_lookups bigintbigint 通过系统查询执行的查找次数。Number of lookups by system queries.
system_updatessystem_updates bigintbigint 通过系统查询执行的更新次数。Number of updates by system queries.
last_system_seeklast_system_seek datetimedatetime 系统上次执行搜索的时间。Time of last system seek.
last_system_scanlast_system_scan datetimedatetime 系统上次执行扫描的时间。Time of last system scan.
last_system_lookuplast_system_lookup datetimedatetime 系统上次执行查找的时间。Time of last system lookup.
last_system_updatelast_system_update datetimedatetime 系统上次执行更新的时间。Time of last system update.
pdw_node_idpdw_node_id intint 适用于: Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseApplies to: Azure SQL 数据仓库Azure SQL Data Warehouse, 并行数据仓库Parallel Data Warehouse

此分布的节点标识符。The identifier for the node that this distribution is on.

注释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.

User_updates计数器指示的引起插入的索引维护级别、 更新或删除基础表或视图上的操作。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.

只要启动 SQL ServerSQL Server (MSSQLSERVER) 服务,计数器就初始化为空。The counters are initialized to empty whenever the SQL ServerSQL Server (MSSQLSERVER) service is started. 而且,当分离或关闭数据库时(例如,由于 AUTO_CLOSE 设置为 ON),便会删除与该数据库关联的所有行。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.

当使用索引时,将行添加到sys.dm_db_index_usage_stats如果行尚不存在的索引。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.

升级到 SQL Server 2008 R2SQL Server 2008 R2SQL Server 2012SQL Server 2012,或 SQL Server 2014SQL Server 2014,会删除 sys.dm_db_index_usage_stats 中的条目。During upgrade to SQL Server 2008 R2SQL Server 2008 R2, SQL Server 2012SQL Server 2012, or SQL Server 2014SQL Server 2014, entries in sys.dm_db_index_usage_stats are removed. 开头 SQL Server 2016SQL Server 2016,与它们之前保留条目 SQL Server 2008 R2SQL Server 2008 R2Beginning with SQL Server 2016SQL Server 2016, entries are retained as they were prior to SQL Server 2008 R2SQL Server 2008 R2.

PermissionsPermissions

SQL ServerSQL Server,需要VIEW SERVER STATE权限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission.
SQL DatabaseSQL Database高级层,需要VIEW DATABASE STATE数据库中的权限。On SQL DatabaseSQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. SQL DatabaseSQL Database标准版和基本层,需要服务器管理员Azure Active Directory 管理员帐户。On SQL DatabaseSQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

另请参阅See Also

索引相关的动态管理视图和函数 (Transact SQL ) Index Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_db_index_physical_stats (Transact-SQL) sys.dm_db_index_physical_stats (Transact-SQL)
sys.dm_db_index_operational_stats (Transact SQL ) sys.dm_db_index_operational_stats (Transact-SQL)
sys.indexes (Transact-SQL) sys.indexes (Transact-SQL)
sys.objects (Transact SQL ) sys.objects (Transact-SQL)
监视和优化性能Monitor and Tune for Performance