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!