Usage Scenarios for sys.dm_db_index_operational_stats

Reading Time: 3 minutes

Returns current lower-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

Memory-optimized indexes do not appear in this DMV.

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 }    
)

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_msThese columns indicate whether there is latch contention on the index or heap, and the significance of the contention.
• row_lock_count and page_lock_countThese 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_msThese 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_msThese columns indicate whether physical I/Os were issued to bring the index or heap pages into memory and how many I/Os were issued.

To identify Lock Escalations

• index_lock_promotion_attempt_count

how many attempts were made to escalate to table locks

• index_lock_promotion_attempt_count

how many times escalations actually succeeded

To analyze Page Split

•  leaf_allocation_count

represents page splits at the leaf

•  nonleaf_allocation_count 

represents splits at the non-leaf levels of an index

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.

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.

 

top three objects with the highest number of escalations:

SELECT TOP 3 OBJECT_NAME(object_id, database_id) object_nm
  ,index_id
  ,partition_number
  ,index_lock_promotion_attempt_count
  ,index_lock_promotion_count
FROM sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL)
ORDER BY index_lock_promotion_count DESC

 

top 3 objects associated with waits on page locks:

SELECT TOP 3 OBJECT_NAME(o.object_id, o.database_id) object_nm
  ,o.index_id
  ,partition_number
  ,page_lock_wait_count
  ,page_lock_wait_in_ms
  ,CASE 
    WHEN mid.database_id IS NULL
      THEN 'N'
    ELSE 'Y'
    END AS missing_index_identified
FROM sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL) o
LEFT OUTER JOIN (
  SELECT DISTINCT database_id
    ,object_id
  FROM sys.dm_db_missing_index_details
  ) AS mid ON mid.database_id = o.database_id
  AND mid.object_id = o.object_id
ORDER BY page_lock_wait_count DESC

 

identifies the top 10 objects involved with page splits

SELECT TOP 10 OBJECT_NAME(object_id, database_id) object_nm
  ,index_id
  ,partition_number
  ,leaf_allocation_count
  ,nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL)
ORDER BY leaf_allocation_count DESC