sys.dm_db_index_operational_stats (Transact-SQL)
适用于： 
SQL Server Azure SQL 数据库 Azure SQL 托管实例
返回数据库中表或索引的每个分区的当前较低级别 I/O、锁定、闩锁和访问方法活动。
内存优化索引不会出现在此 DMV 中。
注意
sys.dm_db_index_operational_stats不返回有关内存优化索引的信息。 有关内存优化索引使用的信息，请参阅sys.dm_db_xtp_index_stats（Transact-SQL）。
语法
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 }
)
参数
database_id |NULL |0 |违约
数据库 ID。 database_id较小。 有效的输入包括数据库的 ID 号、NULL、0 或 DEFAULT。 默认值为 0。 在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL 以返回 SQL Server 实例中的所有数据库的信息。 如果为 database_id指定 NULL，则还必须为 object_id、 index_id和 partition_number指定 NULL。
你可以指定内置函数 DB_ID。
object_id |NULL |0 |违约
索引所打开的表或视图的对象 ID。 object_id为 int。
有效的输入包括表和视图的 ID 号、NULL、0 或 DEFAULT。 默认值为 0。 在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL 可返回指定数据库中的所有表和视图的缓存信息。 如果为 object_id指定 NULL，则还必须为 index_id 和 partition_number指定 NULL。
index_id | 0 |NULL |-1 |违约
索引的 ID。 index_id为 int。有效输入是索引的 ID 号，如果object_id是堆、NULL、-1 或 DEFAULT，则为 0。 默认值为 -1。在此上下文中，NULL、-1 和 DEFAULT 是等价值。
指定 NULL 可返回基表或视图的所有索引的缓存信息。 如果为 index_id指定 NULL，则还必须为 partition_number指定 NULL。
partition_number |NULL |0 |违约
对象中的分区号。 partition_number 为 int。有效输入是 索引或堆、NULL、0 或 DEFAULT 的partion_number 。 默认值为 0。 在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL 可返回索引或堆的所有分区的缓存信息。
partition_number 基于 1。 非分区索引或堆partition_number设置为 1。
返回的表
| 列名称 | 数据类型 | 描述 |
|---|---|---|
| database_id | smallint | 数据库 ID。 在 Azure SQL 数据库中，这些值在单一数据库或弹性池中是唯一的，但在逻辑服务器中不是唯一的。 |
| object_id | int | 表或视图的 ID。 |
| index_id | int | 索引或堆的 ID。 0 = 堆 |
| partition_number | int | 索引或堆中从 1 开始的分区号。 |
| hobt_id | bigint | 适用于： SQL Server 2016（13.x）及更高版本，Azure SQL 数据库。 跟踪列存储索引的内部数据的数据堆或 B 树行集的 ID。 NULL - 这不是内部列存储行集。 有关详细信息，请参阅 sys.internal_partitions（Transact-SQL） |
| 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 | 叶级以上的插入累积计数。 0 = 堆或列存储 |
| nonleaf_delete_count | bigint | 叶级以上的删除累积计数。 0 = 堆或列存储 |
| nonleaf_update_count | bigint | 叶级以上的更新累积计数。 0 = 堆或列存储 |
| leaf_allocation_count | bigint | 索引或堆中的叶级页分配的累积计数。 对于索引，页分配与页拆分对应。 |
| nonleaf_allocation_count | bigint | 叶级以上由页拆分引起的页分配的累积计数。 0 = 堆或列存储 |
| leaf_page_merge_count | bigint | 叶级页合并的累积计数。 对于列存储索引，始终为 0。 |
| nonleaf_page_merge_count | bigint | 叶级以上页合并的累积计数。 0 = 堆或列存储 |
| range_scan_count | bigint | 从索引或堆开始的范围和表扫描的累积计数。 |
| singleton_lookup_count | bigint | 对索引或堆的单行检索的累积计数。 |
| forwarded_fetch_count | bigint | 通过前推记录提取的行计数。 0 = 索引 |
| lob_fetch_in_pages | bigint | 从 LOB_DATA 分配单元检索到的大型对象 (LOB) 页的累积计数。 这些页面包含存储在文本、ntext、image、varchar（max）、nvarchar（max）、varbinary（max）和 xml 的列中的数据。 有关详细信息，请参阅 数据类型 (Transact-SQL)。 |
| lob_fetch_in_bytes | bigint | 检索到的 LOB 数据字节数的累积计数。 |
| lob_orphan_create_count | bigint | 为大容量操作创建的孤立 LOB 值的累积计数。 0 = 非聚集索引 |
| lob_orphan_insert_count | bigint | 大容量操作期间插入的孤立 LOB 值的累积计数。 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 | 仅包含上层 B 树页的page_latch_wait_count子集。 对堆或列存储索引始终为 0。 |
| tree_page_latch_wait_in_ms | bigint | 仅包含高级 B 树页的page_latch_wait_in_ms子集。 对堆或列存储索引始终为 0。 |
| tree_page_io_latch_wait_count | bigint | 仅包含上层 B 树页的page_io_latch_wait_count子集。 对堆或列存储索引始终为 0。 |
| tree_page_io_latch_wait_in_ms | bigint | 仅包含上层 B 树页的page_io_latch_wait_in_ms子集。 对堆或列存储索引始终为 0。 |
| page_compression_attempt_count | bigint | 对于表、索引或索引视图的特定分区，针对 PAGE 级压缩计算的页数。 因为未能极大地节省空间，所以将包括未压缩的页。 对于列存储索引，始终为 0。 |
| page_compression_success_count | bigint | 对于表、索引或索引视图的特定分区，使用 PAGE 压缩功能压缩的数据页数。 对于列存储索引，始终为 0。 |
注意
文档通常使用术语 B 树来引用索引。 在行存储索引中，数据库引擎实现 B+ 树。 这不适用于内存优化表上的列存储索引或索引。 有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。
注解
此动态管理对象不接受 CROSS APPLY 来自和 OUTER APPLY.
可以使用 sys.dm_db_index_operational_stats 跟踪用户必须等待读取或写入表、索引或分区的时间长度，并确定遇到重大 I/O 活动或热点的表或索引。
使用以下各列可标识争用区。
若要分析表或索引分区的常见访问模式，请使用以下列：
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
这些列指示是否已发出物理 I/O 以便将索引或堆页载入内存以及发出的 I/O 数。
列备注
lob_orphan_create_count和lob_orphan_insert_count中的值应始终相等。
对于包含一个或多个 LOB 列的非聚集索引，lob_fetch_in_pages和lob_fetch_in_bytes中的值可以大于零。 有关详细信息，请参阅 Create Indexes with Included Columns。 同样，如果索引包含可推送非行列的列，则列row_overflow_fetch_in_pages和row_overflow_fetch_in_bytes中的值可能大于 0。
如何重置元数据缓存中的计数器
只要表示堆或索引的元数据缓存对象可用，sys.dm_db_index_operational_stats返回的数据就存在。 此数据既不是持久性数据，也不是事务上一致的数据。 这意味着，不能使用这些计数器确定是否已使用索引，或确定上次使用索引的时间。 有关此内容的信息，请参阅sys.dm_db_index_usage_stats（Transact-SQL）。
只要堆或索引的元数据被载入元数据缓存，每列中的值就会被设置为零，且在从元数据缓存中删除缓存对象前会累积统计信息。 因此，活动堆或索引可能始终在其缓存中具有其元数据，并且累积计数可能会反映自上次启动 SQL Server 实例以来的活动。 活动较少的堆或索引的元数据将在使用时移入和移出缓存。 因此，它可能有、也可能没有可用值。 删除索引将导致从内存中删除对应统计信息，且函数不再报告这些统计信息。 对索引执行的其他 DDL 操作可能导致统计信息的值被重置为零。
使用系统函数指定参数值
可以使用 Transact-SQL 函数DB_ID和OBJECT_ID来指定database_id和object_id参数的值。 但是，将无效的值传递给这些函数可能会导致意外结果。 请始终确保使用 DB_ID 或 OBJECT_ID 时返回了有效的 ID。 有关详细信息，请参阅 sys.dm_db_index_physical_stats（Transact-SQL）中的 “备注”部分。
权限
需要下列权限：
CONTROL对数据库中指定对象的权限VIEW DATABASE STATE或VIEW DATABASE PERFORMANCE STATE（SQL Server 2022） 权限，通过使用对象通配符 @object_id = NULL 返回有关指定数据库中所有对象的信息VIEW SERVER STATEVIEW SERVER PERFORMANCE STATE（SQL Server 2022） 使用数据库通配符 @database_id = NULL 返回有关所有数据库的信息的权限
VIEW DATABASE STATE授予允许返回数据库中的所有对象，而不考虑对特定对象拒绝的任何 CONTROL 权限。
拒绝 VIEW DATABASE STATE 拒绝返回数据库中的所有对象，而不考虑授予对特定对象的任何 CONTROL 权限。 此外，指定数据库通配符 @database_id=NULL 时，将省略数据库。
有关详细信息，请参阅动态管理视图和函数（Transact-SQL）。
示例
A. 返回指定表的信息
以下示例返回 AdventureWorks2022 数据库中表的所有索引和分区 Person.Address 的信息。 执行此查询至少需要对 Person.Address 表具有 CONTROL 权限。
重要
使用 Transact-SQL 函数DB_ID并OBJECT_ID返回参数值时，始终确保返回有效的 ID。 如果找不到数据库或对象的名称，例如相应名称不存在或拼写不正确，则两个函数都会返回 NULL。 sys.dm_db_index_operational_stats 函数将 NULL 解释为指定所有数据库或所有对象的通配符值。 由于这可能是无心之举，所以此部分中的示例说明了确定数据库 ID 和对象 ID 的安全方法。
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.Person.Address');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
END;
GO
B. 返回所有表和索引的信息
以下示例返回 SQL Server 实例中所有表和索引的信息。 执行此查询需要 VIEW SERVER STATE 权限。
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);
GO
另请参阅
动态管理视图和函数 (Transact-SQL)
索引相关的动态管理视图和函数 （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.indexes (Transact-SQL)