Transactions generate IO, latching and locking on tables and indexes , while attempting to access data. The sys.dm_db_index_operational_stats DMV returns aggregated data on this activity.

Warning : sys.dm_db_index_operational_stats returns data only as long as the metadata cache object that represents the heap or index is available. A frequently used object is more likely to have metadata availale, whereas a infrequently used object less likely.

The columns row_lock_wait_in_ms + page_lock_wait_in_ms (Block waits ms) focus on lock contention and wait time. This aggregate is a good indicator of Block Wait time. When there is a clear pagelock or rowlock completed it’s recorded . Range locking is not included , which makes the row_lock_wait_in_ms + page_lock_wait_in_ms inaccurate.

How to use ?

1) In conjunction with the sys.dm_db_index_usage_stats, a DBA creates a profile of index usage and blocking. A typical scenario is : sys.dm_db_index_operational_stats returns high Block waits ms for an index. Upon closer analysis the DBA observes that multiple indexes must be updated – even though those indexes are never used.

2) A DBA identifies high blocking on a table. On closer analysis it’s other tables creating the delays.