Locking and blocking measurements: metadata caveats

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

How do we usually obain information about the locking and blocking of the indexes? We run the following query, and we hope that the resultset is accurate:

select db_name(database_id) as DatabaseName
,object_name(object_id) as ObjectName
,row_lock_count + page_lock_count as LocksCount
,row_lock_wait_count + page_lock_wait_count as BlocksCount
,row_lock_wait_in_ms + page_lock_wait_in_ms as BlocksWaitTime
from sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
where db_name(database_id) = DB_NAME()
order by Block_Wait_Time desc

What are the possible inaccuracies? Well, aside from the fact that the query returns a shapeless blob of data (by design the DMVs show cumulative data since the instance startup), there are a few other problems.

– BlocksWaitTime is recorded only when the transaction completes. I.e. if a transaction faces Lock Timeout, then the wait time of this transaction is not accounted
– we have row_lock_count and page_lock_count, but the range lock is not accounted for
– we cannot really tell the ‘story’ of the blocking sequences, and which index is causing the most problems to which other indexes

Any ideas now how to overcome this and how to measure locking, blocking and their chronological placement?!
/Feodor Georgiev