sys.dm_db_index_operational_stats

项目
12/15/2008

更新日期： 2006 年 7 月 17 日

返回数据库中表或索引的每个分区的当前低级 I/O、锁定、闩锁和访问方法活动。

语法

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 | DEFAULT
数据库的 ID。database_id 的数据类型为 smallint。有效的输入包括数据库的 ID 号、NULL、0 或 DEFAULT。默认是 0。在此上下文中，NULL、0 和 DEFAULT 是等价值。

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

可以指定内置函数 DB_ID。如果在不指定数据库名称的情况下使用 DB_ID，则当前数据库的兼容级别必须是 90。

object_id | NULL | 0 | DEFAULT
该索引所基于的表或视图的对象 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 | DEFAULT
索引的 ID。index_id 的数据类型为 int。有效输入包括索引的 ID 号、0（如果 object_id 为堆）、NULL、-1 或 DEFAULT。默认是 -1。在此上下文中，NULL、-1 和 DEFAULT 是等价值。

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

partition_number | NULL | 0 | DEFAULT
对象中的分区号。partition_number 的数据类型为 int。有效输入包括索引或堆的 partion_number、NULL、0 或 DEFAULT。默认是 0。在此上下文中，NULL、0 和 DEFAULT 是等价值。

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

partition_number 从 1 开始。未分区的索引或堆的 partition_number 设置为 1。

返回的表

列名	数据类型	说明
database_id	smallint	数据库 ID。
object_id	int	表或视图的 ID。
index_id	int	索引或堆的 ID。 0 = 堆
partition_number	int	索引或堆中从 1 开始的分区号。
leaf_insert_count	bigint	叶级插入的累积计数。
leaf_delete_count	bigint	叶级删除的累积计数。
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	叶级页合并的累积计数。
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) 页的累积计数。这些页包含存储在类型为 text、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 类型的列中存储的数据。有关详细信息，请参阅行溢出数据超过 8 KB。有关分配单元的详细信息，请参阅表组织和索引组织。
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 闩锁的累积毫秒数。

备注

此动态管理对象不接受来自 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 列中的值可以大于零。有关详细信息，请参阅具有包含性列的索引。同样，对于非聚集索引，如果索引包含可推送到行外的列，则 row_overflow_fetch_in_pages 和 row_overflow_fetch_in_bytes 列中的值也可以大于 0。有关详细信息，请参阅行溢出数据超过 8 KB。

如何重置计数器

仅当表示堆或索引的元数据缓存对象可用时，sys.dm_db_index_operational_stats 返回的数据才存在。此数据既不是持久性数据，也不是事务上一致的数据。这意味着，不能使用这些计数器确定是否已使用索引，或确定上次使用索引的时间。有关重置计数器的信息，请参阅 sys.dm_db_index_usage_stats。

只要堆或索引的元数据被载入元数据缓存，每列中的值就会被设置为零，且在从元数据缓存中删除缓存对象前会累积统计信息。所以，活动堆或索引可能始终将其元数据放在缓存中，且累积计数可能反映自上次启动 SQL Server 以来的活动。活动较少的堆或索引的元数据将在使用时移入和移出缓存。因此，它可能有、也可能没有可用值。删除索引将导致从内存中删除对应统计信息，且该函数不再报告这些统计信息。对索引执行的其他 DDL 操作可能导致统计信息的值被重置为零。

使用系统函数指定参数值

可以使用 Transact-SQL 函数 DB_ID 和 OBJECT_ID 指定 database_id 和 object_id 参数的值。但是，将无效的值传递给这些函数可能会导致意外结果。使用 DB_ID 或 OBJECT_ID 时，请始终确保返回有效的 ID。有关详细信息，请参阅 sys.dm_db_index_physical_stats 中的“备注”部分。

权限

需要下列权限：

对数据库中的指定对象具有 CONTROL 权限
VIEW DATABASE STATE 权限，以便使用对象通配符 @object_id = NULL 返回指定数据库中所有对象的信息
VIEW SERVER STATE 权限，以便使用数据库通配符 @database_id = NULL 返回所有数据库的信息

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

拒绝 VIEW DATABASE STATE 将禁止返回数据库中的所有对象，而不管对特定对象授予的任何 CONTROL 权限。此外，如果指定数据库通配符 @database_id=NULL，则省略数据库。

有关详细信息，请参阅动态管理视图和函数。

示例

A. 返回指定表的信息

以下示例返回 AdventureWorks 数据库中 Person.Address 表的所有索引和分区的信息。执行此查询至少需要对 Person.Address 表具有 CONTROL 权限。

重要提示：
在使用 Transact-SQL 函数 DB_ID 和 OBJECT_ID 返回参数值时，请始终确保返回了有效的 ID。如果找不到数据库或对象的名称，例如相应名称不存在或拼写不正确，则两个函数都会返回 NULL。sys.dm_db_index_operational_stats 函数将 NULL 解释为指定所有数据库或所有对象的通配符值。由于这可能是无心之举，所以此部分举例说明了确定数据库 ID 和对象 ID 的安全方法。

在使用 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'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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

请参阅

更改历史记录

发布日期	历史记录
2006 年 7 月 17 日	新增内容：阐明了在第一个参数中使用 DB_ID() 的数据库兼容级别要求。
2006 年 4 月 14 日	更改的内容：在 database_id、object_id, 和 partition_number 的语法中添加了输入值 0 和 DEFAULT。在 index_id 的语法中添加了输入值 - 1 和 DEFAULT。
2005 年 12 月 5 日	新增内容：添加了“使用系统函数指定参数值”部分。更改的内容：修改了“权限”部分。

2006 年 7 月 17 日

新增内容：

阐明了在第一个参数中使用 DB_ID() 的数据库兼容级别要求。

2006 年 4 月 14 日

更改的内容：

在 database_id、object_id, 和 partition_number 的语法中添加了输入值 0 和 DEFAULT。
在 index_id 的语法中添加了输入值 - 1 和 DEFAULT。

2005 年 12 月 5 日