Sql-server – dm_db_index_operational_stats showing more blocking than extended event report

blockingextended-eventssql server

I’ve implemented the XE to capture blocking on my instance (as per this great article https://www.brentozar.com/archive/2014/03/extended-events-doesnt-hard/#comment-2470072. )

I did this because I kept an eye on the blocking states, using this beauty of a query: 

select 
   db_name(database_id) DB, 
   object_name(object_id) Obj, 
   row_lock_count + page_lock_count No_Of_Locks, 
   row_lock_wait_count + page_lock_wait_count No_Of_Blocks, 
   row_lock_wait_in_ms + page_lock_wait_in_ms Block_Wait_Time_in_ms, 
   index_id
from 
   sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) 
order by 
   Block_Wait_Time_in_ms desc

Occasionally some blocking gets logged to the file, but not as many as I would expect. My expectation being that the number in No_Of_blocks from the query, would correspond with the number of blocks logged to the file as per the XE.

Is there anything the XE might not capture versus the query? Any ideas? Thanks for your feedback!

Best Answer

You are comparing totally different things which are not comparable.

XE trace you are capturing an event called sqlserver.blocked_process_report which is described here.

Here you are capturing all kind of blocking exceeding a fixed amount of time. If it is less than that duration (set up by sp_configure 'blocked process threshold') it will not be captured by this event.

The Blocked Process Report event class indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are waiting on non deadlock-detectable resources.

Your second collection using dm_db_index_operational_stats only counting waits for row and page lock. It is also cumulative since metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache.

Details here: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-operational-stats-transact-sql

  • row_lock_wait_count: Cumulative number of times the Database Engine waited on a row lock.
  • page_lock_wait_count: Cumulative number of times the Database Engine waited on a page lock.
Related Question