The simple guide to SQL Server Cached blocking history with sys.dm_db_index_operational_stats

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.

3) The Blocked Process Report and how to read    explains how to capture blocking . Use the information to analyse queries

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

Read More

sys.dm_db_index_usage_stats

List all indexes of all tables

Top 5 SQL Server DMV for Index Analysis