2

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!

5

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.
  • Thank you @SqlWorldWide to correct my wrong assumptions! So using dm_db_index_operational_stats , this (cummulative) wait time COULD be blocking, but could also be due to any sort of other wait_type like examples below? HADR_FILESTREAM_IOMGR_IOCOMPLETION DIRTY_PAGE_POLL SP_SERVER_DIAGNOSTICS_SLEEP BROKER_EVENTHANDLER So only when I see high cummulative waits , in combination with a lot of LCK_M_X (for example), I should be looking into locking and blocking? I will try this out in test, but are there major drawbacks in changing blocked process threshold to 1 second? – Steez Sep 14 '17 at 13:46
  • You are welcome, glad I could help. You are correct on first comment. Changing threshold to 1 second is entirely depends on what you want to capture, what your application can tolerate etc etc. – SqlWorldWide Sep 14 '17 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.