-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

Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
zapcon
  • 73
  • 2
  • 11

2 Answers2

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.

SqlWorldWide
  • 13,687
  • 3
  • 30
  • 54
2

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

blocked process threshold Server Configuration Option

David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102