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
Data is fascinating.
I make SQL Server faster and more reliable. Database administration isn’t about passing a certified exam, or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family .
My areas of specialty are:
• SQL Server 2019-2008 performance tuning.
• Designing high availability and disaster recovery solutions.
• Securing SQL Server
• SSIS , SSAS
• Designing Data warehouse
A typical case of Plagiarism. Nice job copying Joe Sack’s work as published in TechCommunity – https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/three-usage-scenarios-for-sys-dm-db-index-operational-stats/ba-p/370298