sys.dm_db_index_operational_stats (Transact-SQL)
Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
.gif "Topic link icon")
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 | NULL | 0 | DEFAULT
ID of the database. database_id is smallint. Valid inputs are the ID number of a database, NULL, 0, or DEFAULT. The default is 0. NULL, 0, and DEFAULT are equivalent values in this context.Specify NULL to return information for all databases in the instance of SQL Server. If you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number.
The built-in function DB_ID can be specified. When using DB_ID without specifying a database name, the compatibility level of the current database must be 90.
object_id | NULL | 0 | DEFAULT
Object ID of the table or view the index is on. object_id is int.Valid inputs are the ID number of a table and view, NULL, 0, or DEFAULT. The default is 0. NULL, 0, and DEFAULT are equivalent values in this context.
Specify NULL to return cached information for all tables and views in the specified database. If you specify NULL for object_id, you must also specify NULL for index_id and partition_number.
index_id | 0 | NULL | -1 | DEFAULT
ID of the index. index_id is int. Valid inputs are the ID number of an index, 0 if object_id is a heap, NULL, -1, or DEFAULT. The default is -1, NULL, -1, and DEFAULT are equivalent values in this context.Specify NULL to return cached information for all indexes for a base table or view. If you specify NULL for index_id, you must also specify NULL for partition_number.
partition_number | NULL | 0 | DEFAULT
Partition number in the object. partition_number is int. Valid inputs are the partion_number of an index or heap, NULL, 0, or DEFAULT. The default is 0. NULL, 0, and DEFAULT are equivalent values in this context.Specify NULL to return cached information for all partitions of the index or heap.
partition_number is 1-based. A nonpartitioned index or heap has partition_number set to 1.
Table Returned
Column name |
Data type |
Description |
|---|---|---|
database_id |
smallint |
Database ID. |
object_id |
int |
ID of the table or view. |
index_id |
int |
ID of the index or heap. 0 = Heap |
partition_number |
int |
1-based partition number within the index or heap. |
leaf_insert_count |
bigint |
Cumulative count of leaf-level inserts. |
leaf_delete_count |
bigint |
Cumulative count of leaf-level deletes. |
leaf_update_count |
bigint |
Cumulative count of leaf-level updates. |
leaf_ghost_count |
bigint |
Cumulative count of leaf-level rows that are marked as deleted, but not yet removed. These rows are removed by a cleanup thread at set intervals. This value does not include rows that are retained, because of an outstanding snapshot isolation transaction. For more information about snapshot isolation transactions, see Row Versioning-based Isolation Levels in the Database Engine. |
nonleaf_insert_count |
bigint |
Cumulative count of inserts above the leaf level. 0 = Heap |
nonleaf_delete_count |
bigint |
Cumulative count of deletes above the leaf level. 0 = Heap |
nonleaf_update_count |
bigint |
Cumulative count of updates above the leaf level. 0 = Heap |
leaf_allocation_count |
bigint |
Cumulative count of leaf-level page allocations in the index or heap. For an index, a page allocation corresponds to a page split. |
nonleaf_allocation_count |
bigint |
Cumulative count of page allocations caused by page splits above the leaf level. 0 = Heap |
leaf_page_merge_count |
bigint |
Cumulative count of page merges at the leaf level. |
nonleaf_page_merge_count |
bigint |
Cumulative count of page merges above the leaf level. 0 = Heap |
range_scan_count |
bigint |
Cumulative count of range and table scans started on the index or heap. |
singleton_lookup_count |
bigint |
Cumulative count of single row retrievals from the index or heap. |
forwarded_fetch_count |
bigint |
Count of rows that were fetched through a forwarding record. 0 = Indexes |
lob_fetch_in_pages |
bigint |
Cumulative count of large object (LOB) pages retrieved from the LOB_DATA allocation unit. These pages contain data that is stored in columns of type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml. For more information, see Data Types (Transact-SQL). For more information about allocation units, see Table and Index Organization. |
lob_fetch_in_bytes |
bigint |
Cumulative count of LOB data bytes retrieved. |
lob_orphan_create_count |
bigint |
Cumulative count of orphan LOB values created for bulk operations. 0 = Nonclustered index |
lob_orphan_insert_count |
bigint |
Cumulative count of orphan LOB values inserted during bulk operations. 0 = Nonclustered index |
row_overflow_fetch_in_pages |
bigint |
Cumulative count of row-overflow data pages retrieved from the ROW_OVERFLOW_DATA allocation unit. These pages contain data stored in columns of type varchar(n), nvarchar(n), varbinary(n), and sql_variant that has been pushed off-row. For more information, see Row-Overflow Data Exceeding 8 KB. For more information about allocation units, see Table and Index Organization. |
row_overflow_fetch_in_bytes |
bigint |
Cumulative count of row-overflow data bytes retrieved. |
column_value_push_off_row_count |
bigint |
Cumulative count of column values for LOB data and row-overflow data that is pushed off-row to make an inserted or updated row fit within a page. |
column_value_pull_in_row_count |
bigint |
Cumulative count of column values for LOB data and row-overflow data that is pulled in-row. This occurs when an update operation frees up space in a record and provides an opportunity to pull in one or more off-row values from the LOB_DATA or ROW_OVERFLOW_DATA allocation units to the IN_ROW_DATA allocation unit. For more information about allocation units, see Table and Index Organization. |
row_lock_count |
bigint |
Cumulative number of row locks requested. |
row_lock_wait_count |
bigint |
Cumulative number of times the Database Engine waited on a row lock. |
row_lock_wait_in_ms |
bigint |
Total number of milliseconds the Database Engine waited on a row lock. |
page_lock_count |
bigint |
Cumulative number of page locks requested. |
page_lock_wait_count |
bigint |
Cumulative number of times the Database Engine waited on a page lock. |
page_lock_wait_in_ms |
bigint |
Total number of milliseconds the Database Engine waited on a page lock. |
index_lock_promotion_attempt_count |
bigint |
Cumulative number of times the Database Engine tried to escalate locks. |
index_lock_promotion_count |
bigint |
Cumulative number of times the Database Engine escalated locks. |
page_latch_wait_count |
bigint |
Cumulative number of times the Database Engine waited, because of latch contention. |
page_latch_wait_in_ms |
bigint |
Cumulative number of milliseconds the Database Engine waited, because of latch contention. |
page_io_latch_wait_count |
bigint |
Cumulative number of times the Database Engine waited on an I/O page latch. |
page_io_latch_wait_in_ms |
bigint |
Cumulative number of milliseconds the Database Engine waited on a page I/O latch. |
tree_page_latch_wait_count |
bigint |
Subset of page_latch_wait_count that includes only the upper-level B-tree pages. Always 0 for a heap. |
tree_page_latch_wait_in_ms |
bigint |
Subset of page_latch_wait_in_ms that includes only the upper-level B-tree pages. Always 0 for a heap. |
tree_page_io_latch_wait_count |
bigint |
Subset of page_io_latch_wait_count that includes only the upper-level B-tree pages. Always 0 for a heap. |
tree_page_io_latch_wait_in_ms |
bigint |
Subset of page_io_latch_wait_in_ms that includes only the upper-level B-tree pages. Always 0 for a heap. |
page_compression_attempt_count |
bigint |
Number of pages that were evaluated for PAGE level compression for specific partitions of a table, index, or indexed view. Includes pages that were not compressed because significant savings could not be achieved. |
page_compression_success_count |
bigint |
Number of data pages that were compressed by using PAGE compression for specific partitions of a table, index, or indexed view. |
Remarks
This dynamic management object does not accept correlated parameters from CROSS APPLY and OUTER APPLY.
You can use sys.dm_db_index_operational_stats to track the length of time that users must wait to read or write to a table, index, or partition, and identify the tables or indexes that are encountering significant I/O activity or hot spots.
Use the following columns to identify areas of contention.
To analyze a common access pattern to the table or index partition, use these columns:
leaf_insert_count
leaf_delete_count
leaf_update_count
leaf_ghost_count
range_scan_count
singleton_lookup_count
To identify latching and locking contention, use these columns:
page_latch_wait_count and page_latch_wait_in_ms
These columns indicate whether there is latch contention on the index or heap, and the significance of the contention.
row_lock_count and page_lock_count
These columns indicate how many times the Database Engine tried to acquire row and page locks.
row_lock_wait_in_ms and page_lock_wait_in_ms
These columns indicate whether there is lock contention on the index or heap, and the significance of the contention.
To analyze statistics of physical I/Os on an index or heap partition
page_io_latch_wait_count and page_io_latch_wait_in_ms
These columns indicate whether physical I/Os were issued to bring the index or heap pages into memory and how many I/Os were issued.
Column Remarks
The values in lob_orphan_create_count and lob_orphan_insert_count should always be equal.
The value in the columns lob_fetch_in_pages and lob_fetch_in_bytes can be greater than zero for nonclustered indexes that contain one or more LOB columns as included columns. For more information, see Index with Included Columns. Similarly, the value in the columns row_overflow_fetch_in_pages and row_overflow_fetch_in_bytes can be greater than 0 for nonclustered indexes if the index contains columns that can be pushed off-row. For more information, see Row-Overflow Data Exceeding 8 KB.
How the Counters in the Metadata Cache Are Reset
The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats (Transact-SQL).
The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.
Using System Functions to Specify Parameter Values
You can use the Transact-SQL functions DB_ID and OBJECT_ID to specify a value for the database_id and object_id parameters. However, passing values that are not valid to these functions may cause unintended results. Always make sure that a valid ID is returned when you use DB_ID or OBJECT_ID. For more information, see the Remarks section in sys.dm_db_index_physical_stats (Transact-SQL).
Permissions
Requires the following permissions:
CONTROL permission on the specified object within the database
VIEW DATABASE STATE permission to return information about all objects within the specified database, by using the object wildcard @object_id = NULL
VIEW SERVER STATE permission to return information about all databases, by using the database wildcard @database_id = NULL
Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.
Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. Also, when the database wildcard @database_id=NULL is specified, the database is omitted.
For more information, see Dynamic Management Views and Functions (Transact-SQL).
Examples
A. Returning information for a specified table
The following example returns information for all indexes and partitions of the Person.Address table in the AdventureWorks2008R2 database. Executing this query requires, at a minimum, CONTROL permission on Person.Address table.
Important
When you are using the Transact-SQL functions DB_ID and OBJECT_ID to return a parameter value, always ensure that a valid ID is returned. If the database or object name cannot be found, such as when they do not exist or are spelled incorrectly, both functions will return NULL. The sys.dm_db_index_operational_stats function interprets NULL as a wildcard value that specifies all databases or all objects. Because this can be an unintentional operation, the examples in this section demonstrate the safe way to determine database and object IDs.
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.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. Returning information for all tables and indexes
The following example returns information for all tables and indexes within the instance of SQL Server. Executing this query requires VIEW SERVER STATE permission.
SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);
GO
See Also