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 whioch are creating the delaying
select db_name(database_id) DB, QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id)) + N'.' + QUOTENAME(OBJECT_NAME(object_id, database_id)) ObjDetails, row_lock_wait_in_ms + page_lock_wait_in_ms Block_Wait_Time_in_ms from sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) order by Block_Wait_Time_in_ms desc,ObjDetails desc