-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

Improve this question
5
  • Tag your question with appropriate RDBMS. I believe you are asking about SQl Server ?
    – Shanky
    Commented Aug 24, 2017 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
    Commented Aug 24, 2017 at 9:48
  • Do you mean you are looking for lead blocking sessions?
    – John K. N.
    Commented Aug 24, 2017 at 11:38
  • 1
    Possible duplicate of why a session becomes a head blocker
    – John K. N.
    Commented Aug 24, 2017 at 11:39
  • This is not a dupe since this question is about history via DMVs.
    – Hannah Vernon ♦
    Commented Aug 24, 2017 at 21:18

2 Answers 2

Reset to default
2

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.

Improve this answer
2

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

blocked process threshold Server Configuration Option

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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