sys.dm_db_index_operational_stats（Transact-SQL）

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Microsoft Fabric 中的 SQL 数据库

返回数据库中表或索引的每个分区的较低级别数据访问、锁定和闩锁统计信息。

Transact-SQL 语法约定

Syntax

sys.dm_db_index_operational_stats (
    { database_id | NULL | 0 | DEFAULT }
    , { object_id | NULL | 0 | DEFAULT }
    , { index_id | 0 | NULL | -1 | DEFAULT }
    , { partition_number | NULL | 0 | DEFAULT }
)

Arguments

{ database_id |空 |0 | DEFAULT }

数据库 ID。 database_id较小。 有效的输入是数据库的ID号、 NULL、、 0或 DEFAULT。 默认值为 0。 NULL、 0和 DEFAULT 在此上下文中是等效的值。

指定 NULL 为 SQL Server 实例中的所有数据库返回信息。 如果指定database_id，则还必须NULL指定object_id、NULL和partition_number。

可以指定内置函数 DB_ID 。

{ object_id |空 |0 | DEFAULT }

索引所打开的表或视图的对象 ID。 object_id为 int。

有效的输入是表和视图的ID编号、 NULL、 0或 DEFAULT。 默认值为 0。 NULL、 0和 DEFAULT 在此上下文中是等效的值。

指定 NULL 以返回指定数据库中所有表和视图的信息。 如果指定object_id，则还必须指定NULLindex_id和NULL。

{ index_id | 0 |空 |-1 | DEFAULT }

索引的 ID。 index_id是智力。有效输入是索引的ID编号，0如果object_id是一个堆、NULL、或-1DEFAULT。 默认值为 -1。 NULL、 -1和 DEFAULT 在此上下文中是等效的值。

指定 NULL 返回基表或视图的所有索引的信息。 如果指定NULLindex_id，则还必须为NULL指定。

{ partition_number |空 |0 | DEFAULT }

对象中的分区号。 partition_numberint。有效输入是索引或堆、、NULL或 0的 DEFAULT。 默认值为 0。 NULL、 0和 DEFAULT 在此上下文中是等效的值。

指定 NULL 返回索引或堆所有分区的信息。

partition_number 基于 1。 非分区索引或堆partition_number设置为 1。

返回的表

列名称	数据类型	说明
database_id	smallint	数据库 ID。 在 Azure SQL 数据库中，这些值在单一数据库或弹性池中是唯一的，但在逻辑服务器中不是唯一的。
object_id	int	表或视图的 ID。 更多信息请参见 sys.objects。
index_id	int	索引或堆的 ID。 更多信息请参见 sys.indexes。
partition_number	int	索引或堆中从 1 开始的分区号。 更多信息请参见 sys.partitions。
hobt_id	bigint	跟踪列存储索引的内部数据的数据堆或 B 树行集的 ID。 NULL - 这不是内部的列存储行集。 有关详细信息，请参阅 sys.internal_partitions。
leaf_insert_count	bigint	叶级插入的累积计数。 有关索引级别的详细信息，请参阅 索引体系结构和设计指南。
leaf_delete_count	bigint	叶级删除的累积计数。 leaf_delete_count 只有在未被标记为“幽灵”的已删除记录时才会增加。 对于首先虚影的已删除记录， leaf_ghost_count 将改为递增。
leaf_update_count	bigint	叶级更新的累积计数。
leaf_ghost_count	bigint	标记为已删除但尚未删除的叶级行的累积计数。 这个计数不包括那些立即被删除且未标记为幽灵的记录。 清理线程按设置间隔删除虚影行。 该值不包括因未完成快照事务而被保留的幽灵行。
nonleaf_insert_count	bigint	叶级以上的插入累积计数。 仅适用于 B 树索引。 0 表示堆或列存储索引。
nonleaf_delete_count	bigint	叶级以上的删除累积计数。 仅适用于 B 树索引。 0 表示堆或列存储索引。
nonleaf_update_count	bigint	叶级以上的更新累积计数。 仅适用于 B 树索引。 0 表示堆或列存储索引。
leaf_allocation_count	bigint	索引或堆中叶级页分配的累积计数。 对于索引，页分配与页拆分对应。
nonleaf_allocation_count	bigint	叶级以上由页拆分引起的页分配的累积计数。 仅适用于 B 树索引。 0 表示堆或列存储索引。
leaf_page_merge_count	bigint	叶级页合并的累积计数。 对于列存储索引，始终为 0。
nonleaf_page_merge_count	bigint	叶级以上页合并的累积计数。 仅适用于 B 树索引。 0 表示堆或列存储索引。
range_scan_count	bigint	从索引或堆开始的范围和表扫描的累积计数。
singleton_lookup_count	bigint	对索引或堆的单行检索的累积计数。
forwarded_fetch_count	bigint	通过前推记录提取的行计数。 仅适用于堆，B 树索引的 0。
lob_fetch_in_pages	bigint	从 LOB_DATA 分配单元检索的大型对象 （LOB） 页的累积计数。 这些页面包含的数据，存储在文本、ntext、image、varchar（max）、nvarchar（max）、varbinary（max）、xml 和 json 等列中。 有关详细信息，请参阅数据类型。
lob_fetch_in_bytes	bigint	检索到的 LOB 数据字节数的累积计数。
lob_orphan_create_count	bigint	为大容量操作创建的孤立 LOB 值的累积计数。 仅适用于堆和 B 树聚集索引，对于非聚集索引和列存储索引为 0。
lob_orphan_insert_count	bigint	大容量操作期间插入的孤立 LOB 值的累积计数。 仅适用于堆和 B 树聚集索引，对于非聚集索引和列存储索引为 0。
row_overflow_fetch_in_pages	bigint	从 ROW_OVERFLOW_DATA 分配单元检索的行溢出数据页的累积计数。 这些页面包含存储在类型、varchar(n)nvarchar(n)varbinary(n)行和sql_variant大型行的列中的数据。
row_overflow_fetch_in_bytes	bigint	检索到的行溢出数据字节数的累积计数。
column_value_push_off_row_count	bigint	已推出行外以使插入或更新的行可容纳在页中的 LOB 数据和行溢出数据的列值累积计数。
column_value_pull_in_row_count	bigint	已请求到行内的 LOB 数据和行溢出数据的列值的累积计数。 当更新操作释放记录中的空间，并有机会将一个或多个行外值从 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元拉取到分配单元时， IN_ROW_DATA 会出现这种情况。
row_lock_count	bigint	请求的行锁的累积数量。
row_lock_wait_count	bigint	在行锁上等待数据库引擎的累积次数。
row_lock_wait_in_ms	bigint	行锁上等待数据库引擎的总毫秒数。
page_lock_count	bigint	请求的页锁的累积数量。
page_lock_wait_count	bigint	数据库引擎在页锁上等待的累积次数。
page_lock_wait_in_ms	bigint	数据库引擎在页锁上等待的总毫秒数。
index_lock_promotion_attempt_count	bigint	数据库引擎尝试升级锁的累积次数。
index_lock_promotion_count	bigint	数据库引擎升级锁的累积次数。
page_latch_wait_count	bigint	数据库引擎等待获取闩锁的累积次数。
page_latch_wait_in_ms	bigint	数据库引擎等待获取闩锁的累积毫秒数。
page_io_latch_wait_count	bigint	数据库引擎在页 I/O 闩锁上等待的累积次数。
page_io_latch_wait_in_ms	bigint	数据库引擎在页 I/O 闩锁上等待的累积毫秒数。
tree_page_latch_wait_count	bigint	其中子 page_latch_wait_count 集仅包含上级 B 树页。 对堆或列存储索引始终为 0。
tree_page_latch_wait_in_ms	bigint	其中子 page_latch_wait_in_ms 集仅包含上级 B 树页。 对堆或列存储索引始终为 0。
tree_page_io_latch_wait_count	bigint	其中子 page_io_latch_wait_count 集仅包含上级 B 树页。 对堆或列存储索引始终为 0。
tree_page_io_latch_wait_in_ms	bigint	其中子 page_io_latch_wait_in_ms 集仅包含上级 B 树页。 对堆或列存储索引始终为 0。
page_compression_attempt_count	bigint	针对 PAGE 特定表格、索引或索引视图的分区，评估过的层级压缩页面数量。 包含因无法实现显著节省而未压缩的页面。 对于列存储索引，始终为 0。
page_compression_success_count	bigint	通过压缩对表格、索引或索引视图的特定分区进行压缩 PAGE 的数据页数量。 对于列存储索引，始终为 0。
version_generated_inrow	bigint	在堆或 B 树中为更新、合并或插入虚影操作生成的有效负载的行内版本的累积计数。 行内版本直接将旧行映像（或差异）存储在行中，避免访问版本存储。 此计数是一个超集，包括按 insert_over_ghost_version_inrow其计数的版本。 有关行内版本和行外版本的详细信息，请参阅 持久版本存储（PVS）使用的空间。
version_generated_offrow	bigint	推送到堆、B 树或 LOB 删除、更新、合并或插入虚影操作的行外存储的累积版本计数。 当旧行图像无法保持在行中时，会生成离行版本。 此计数是一个超集，包括按 ghost_version_offrow 和 insert_over_ghost_version_offrow计数的版本。
ghost_version_inrow	bigint	删除或更新（作为删除执行后插入）的累积计数将现有行标记为具有行内版本控制信息的虚影。 行内版本仅存储事务时间戳和零长度有效负载，因此撤消删除只需要取消托管行。
ghost_version_offrow	bigint	删除或更新（作为删除执行后插入）的累积计数将现有行或 LOB 列数据推送到行外存储，保留行中的存根以获取版本控制信息。 此计数器在虚影操作期间一 version_generated_offrow 起递增。
insert_over_ghost_version_inrow	bigint	为 B 树插入虚影操作生成的有效负载的行内版本的累积计数。 当将新行插入到以前虚影记录的槽中（从显式删除后跟插入），或者从作为删除后跟插入的更新或合并实现的更新或合并时发生。 此计数器是 . 的 version_generated_inrow子集。
insert_over_ghost_version_offrow	bigint	在 B 树插入虚影操作期间，现有虚影行被推送到行外存储的累积计数，在新插入的行中保留存根以获取版本控制信息。 此计数器是 . 的 version_generated_offrow子集。
compaction_attempt_count	bigint	指数自压缩尝试的累计次数。 更多信息请参见自动索引压缩（预览）。
compaction_complete_count	bigint	完成的指数自合成的累计计数。
compaction_skip_count	bigint	累计跳过的索引自合成计数。 有关跳跃原因的更多信息，请参见 “使用扩展事件监控压缩统计”。
compaction_ineligible_count	bigint	累计压缩尝试次数被跳过，因为某页不符合自动压缩的资格。
compaction_failure_count	bigint	累计失败压实尝试次数。
compaction_row_move_count	bigint	通过自动压缩，从一页移动到另一页的累计行数。
compaction_page_deallocation_count	bigint	将所有行移至另一页后被分配的累计页面数。

注释

文档在提到索引时一般使用 B 树这个术语。 在行存储索引中，数据库引擎实现了 B+ 树。 这不适用于列存储索引或内存优化表上的索引。 有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。

注解

此函数不会返回有关内存优化表上的索引的信息。 有关内存优化表索引的信息，请参见 sys.dm_db_xtp_index_stats。

此函数不接受来自和 CROSS APPLY. 的关联参数OUTER APPLY。

可用于 sys.dm_db_index_operational_stats 跟踪表、索引或分区的数据读取和写入操作统计信息，以及锁定、页闩锁和页 I/O 闩锁统计信息。 可以标识遇到重大活动或争用的表、索引和分区。

统计信息在分区级别提供，并且是累加的。 这意味着可以通过在 T-SQL 中编写聚合查询来获取索引级别或表级统计信息。 有关详细信息，请参阅 索引扫描并查找所有表 示例。

若要分析表、索引或分区的读取和写入操作统计信息，请使用以下列：

• leaf_insert_count
• leaf_delete_count
• leaf_update_count
• leaf_ghost_count
• range_scan_count
• singleton_lookup_count

若要识别闩锁争用，请使用以下列：

• page_latch_wait_count
• page_latch_wait_in_ms

若要标识锁争用，请使用以下列：

• row_lock_count
• page_lock_count
• row_lock_wait_in_ms
• page_lock_wait_in_ms

若要分析物理 I/O 统计信息，请使用以下列：

• page_io_latch_wait_count
• page_io_latch_wait_in_ms

列备注

列中 lob_fetch_in_pages 的值， lob_fetch_in_bytes 对于包含一个或多个 LOB 列的非聚集索引，其值可以大于零，这些索引包含一个或多个 LOB 列作为包含的列。 有关详细信息，请参阅 创建包含列的索引。 同样，如果索引包含row_overflow_fetch_in_pages，则列中row_overflow_fetch_in_bytes的值对于非聚集索引可以大于 0。

如何重置元数据缓存中的计数器

仅当表示堆或 B 树的元数据缓存对象可用时，才存在所 sys.dm_db_index_operational_stats 返回的数据。 这些数据不是持久的。 这意味着你无法用这些计数器来确定索引是否被使用，或者该索引最后使用的时间。 相反，使用 sys.dm_db_index_usage_stats。

每当将堆或 B 树的元数据引入元数据缓存时，每个数值列的值都将设置为零。 统计信息将累积，直到从元数据缓存中删除缓存对象。 活动堆或 B 树通常在其缓存中具有其元数据，并且累积计数反映自上次启动数据库引擎实例以来的活动。 一个较不活跃的堆或B树的元数据可能会随着使用而进出缓存，尤其是在数据库引擎实例内存紧张时。 因此，索引操作统计信息有时可能不会反映在 sys.dm_db_index_operational_stats其中。 这并不常见。

从缓存中删除统计信息，如果删除表或索引，或者分区被截断，则此函数不再报告统计信息。 针对索引的其他 DDL 操作可能会导致统计信息的值重置为零。

使用系统函数指定参数值

可以使用 Transact-SQL 函数DB_ID和OBJECT_ID来指定database_id和object_id参数的值。 然而，传递不有效的值可能会产生意想不到的结果。 始终确保在使用或时DB_IDOBJECT_ID返回有效的 ID。 有关详细信息，请参阅 指定表的返回信息。

Permissions

需要下列权限：

• CONTROL 对数据库中指定对象的权限

• VIEW DATABASE STATE 或者 VIEW DATABASE PERFORMANCE STATE 在未指定 的 @object_id 值时返回指定数据库中所有对象的信息。

• VIEW SERVER STATE 或者 VIEW SERVER PERFORMANCE STATE 在未指定值 @database_id 时返回所有数据库信息的权限。

VIEW DATABASE STATE授予或VIEW SERVER PERFORMANCE STATE允许返回数据库中的所有对象，而不考虑拒绝对特定对象的任何CONTROL权限。

拒绝 VIEW DATABASE STATE 或 VIEW SERVER PERFORMANCE STATE 禁止返回数据库中的所有对象，而不考虑授予对特定对象的任何 CONTROL 权限。

欲了解更多信息，请参见 系统动态管理视图和功能。

Examples

返回指定表的信息

以下示例返回了 AdventureWorks2025 数据库中该表的所有索引和分区 Person.Address 信息。

重要

使用 Transact-SQL 函数DB_IDOBJECT_ID和返回参数值时，务必确保返回的ID有效。 如果找不到数据库或对象名称，例如当数据库或对象名称不存在或拼写错误时，这两个函数都会返回 NULL。 该 sys.dm_db_index_operational_stats 函数解释 NULL 为指定所有数据库或所有对象的通配符值。 由于这可能是无心之举，所以此部分中的示例说明了确定数据库 ID 和对象 ID 的安全方法。

DECLARE @db_id AS INT = DB_ID(N'AdventureWorks2025');
DECLARE @object_id AS INT = OBJECT_ID(N'AdventureWorks2025.Person.Address');

SELECT *
FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL)
WHERE @db_id IS NOT NULL
      AND @object_id IS NOT NULL;

返回所有表和索引的信息

以下示例返回数据库引擎实例上所有表和索引的信息。

SELECT *
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);

索引扫描并查找所有表

以下示例聚合分区级别数据，以返回当前数据库中所有表的索引查找和扫描统计信息。

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS object_name,
       COUNT(DISTINCT(index_id)) AS index_count,
       COUNT(DISTINCT(partition_number)) AS partition_count,
       SUM(range_scan_count) AS index_scan_count,
       SUM(singleton_lookup_count) AS index_seek_count
FROM sys.dm_db_index_operational_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT)
GROUP BY OBJECT_SCHEMA_NAME(object_id),
         OBJECT_NAME(object_id)
ORDER BY schema_name, object_name;

相关内容

• 系统动态管理视图与功能
• 索引相关的动态管理视图和函数 （Transact-SQL）
• 监控和调优性能
• sys.dm_db_index_physical_stats（Transact-SQL）
• sys.dm_db_index_usage_stats（Transact-SQL）
• sys.dm_os_latch_stats（Transact-SQL）
• sys.dm_db_partition_stats（Transact-SQL）
• sys.allocation_units（Transact-SQL）
• sys.partitions （Transact-SQL）
• sys.indexes （Transact-SQL）