sys.dm_db_index_operational_stats (Transact-SQL)

Switch View

sys.dm_db_index_operational_stats (...

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (October 2009)

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.

Topic link icon Transact-SQL Syntax Conventions

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 AdventureWorks 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.

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'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. 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

Column Remarks

How the Counters in the Metadata Cache Are Reset

Using System Functions to Specify Parameter Values

A. Returning information for a specified table

B. Returning information for all tables and indexes

Reference

Other Resources

Help and Information