-1

Is there any method of looking for Head Blocking history via DMVs. I have been able to find the subsequent wait details via Extended Events but there doesn't seem to be much information about what caused the intial blocking.

Any thoughts please.

Thanks

  • Tag your question with appropriate RDBMS. I believe you are asking about SQl Server ? – Shanky Aug 24 '17 at 9:05
  • have a look at the answers to this question basically one way to start could be collecting your blocking processes and save them somewhere, then you can have your statistics over time. – Marcello Miorelli Aug 24 '17 at 9:48
  • Do you mean you are looking for lead blocking sessions? – John aka hot2use Aug 24 '17 at 11:38
  • 1
    Possible duplicate of why a session becomes a head blocker – John aka hot2use Aug 24 '17 at 11:39
  • This is not a dupe since this question is about history via DMVs. – Max Vernon Aug 24 '17 at 21:18
1

Unless you collect you session state by using some of the query (answer) in the comment section there is no way to find the leading blocker history.

I personally use Adam Machanic's sp_whoisactive with @find_block_leaders = 1 and @sort_order = '[blocked_session_count] DESC' which works great. If you want a history you can save the output at xx min interval based on your need.

1

Not in the DMVs, but you can turn on the Blocked Process Report:

blocked process threshold Server Configuration Option

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.