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 ,index_id 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?!