June 23, 2020 at 6:55 pm
if blocked process threshold is set = 30 in our server,
Here on line 2 it says 'blocking report will not be generated for queries blocking for duration less than 30 seconds'
My question is "generated" by WHOM or by what exactly ..
the reason I am asking is IF threshold is set to 30, WILL the other tools such as Query Store, Extended Events, or sp_WhoIsActive , ....,etc.. , still see and log the blocking processes shorter than 30 minutes?
Likes to play Chess
June 23, 2020 at 7:33 pm
To answer your first question, in the article you linked it says:
"Blocked process threshold uses the deadlock monitor background thread to walk through the list of tasks waiting for a time greater than or multiples of the configured threshold. The event is generated once per reporting interval for each of the blocked tasks."
To answer your second question, blocking processes will still be seen in some dynamic management views, such as sys.dm_exec_requests and sys.dm_os_waiting_tasks so sp_WhoIsActive may see them, but things dependent on the event being generated will not happen, such as Extended Events, Event Notifications, or Traces.
June 24, 2020 at 10:29 am
The Blocked Process Report is an event within SQL Server. That's what you're setting the threshold for. You can read about it here. And yes, some monitoring tools use this event to show blocked processes. You can consume it yourself through Extended Events.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply