1

I'm trying to work with the blocked process reports captured using Extended Events and, while I can find numerous blog articles and Q&A about specific details (e.g.1 2 3), I can't find any documentation from MS about the contents of the report & how to interpret each field.

For example, I'd like to know:

  • What are clientoption1and clientoption2
  • Why do I sometimes get an object_id that doesn't exist in my database (like this question)
  • What does index_id indicate (probably answered at link above)
  • How to decode waitresource (here's a start)
  • ... and the list goes on.

I don't just want answers to these questions, I want something more complete.

Is this an undocumented feature or am I just crap at finding it?

Rory
  • 1,300
  • 9
  • 11
  • 1
    https://dba.stackexchange.com/questions/238982/blocked-process-report / https://michaeljswart.com/2011/04/a-new-way-to-examine-blocked-process-reports/ might be useful resources – Aaron Bertrand Sep 29 '20 at 21:21
  • Thanks, yes I've found those helpful. But still, nothing comprehensive from MS. – Rory Sep 29 '20 at 21:27
  • 2
    If you only want something from MS, you might want to contact MS. – Aaron Bertrand Sep 29 '20 at 21:30
  • Well, I'm hoping for something comprehensive and well-structured, no you're right it needn't be from MS. Most of the articles and answers I find are quite narrowly focused or just a poor rewrite of someone else's post. Many are also really old, so possibly superseded by better ways of doing things. – Rory Sep 29 '20 at 21:36
  • 1
    Better ways of doing things with the blocked process report that hasn't changed since 2005? Meh, I doubt it. – Aaron Bertrand Sep 29 '20 at 21:37
  • Perhaps the contents haven't changed (how is one supposed to know when there's no documentation and you're learning about it for the first time), but ways of working with it certainly have ... for example Michael's blog post that you linked to is out of date and doesn't mention supporting Extended Events (although the github project does). – Rory Sep 29 '20 at 21:52
  • Sure, blog posts are not going to be kept up to date forever. Can you imagine how many blog posts talk about splitting or aggregating strings but were written before STRING_SPLIT and STRING_AGG were introduced? I probably have at least a dozen myself. Am I going to go back and rewrite them all? Fat chance. People can still learn from them but if they're not finding newer approaches from myself and others there's not much as an author I'm able to do about that. – Aaron Bertrand Sep 29 '20 at 22:18
  • Also, you saw in Michael's article this link, right, where he does have XE support? – Aaron Bertrand Sep 29 '20 at 22:33

2 Answers2

0

fwiw (which isn't much), I asked MS about this and they filed it under "sure, we'll do that later", recommending these docs for now:

  • Blocked Process Report Event Class - describes the event class (I think the article is talking about traces not extended events, but presumably it's still relevant), but doesn't give info about the xml report contents.
  • Understand and resolve SQL Server blocking problems - quite a comprehensive article but unfortunately doesn't talk about the contents of the blocked_process_report.
  • This article addresses the xml_deadlock_report Extended Event for deadlock reports. That's not for blocked process reports but it looks like some of the fields that are documented are the same
Rory
  • 1,300
  • 9
  • 11
0

Does this linke help you at all? https://manualzz.com/doc/25790559/sql-server-event-class-reference---microsoft-docs help?

Blocked Process Report Event Class in particular.

Roy Latham
  • 141
  • 4
  • Not sure, it’s 702 pages long :-) Which bit? – Rory Oct 18 '21 at 14:56
  • From the table of contents, I followed Blocked Process Report Event Class which took me to: https://manualzz.com/viewer_next/web/manual?file=%2F%2Fs3p.manualzz.com%2Fstore%2Fdata%2F025790559.pdf%3Fk%3DAwAAAXyUO84UAAACWETh-Y8jTAQcCLIbegdtxJilafzJ&img=%2F%2Fs3.manualzz.com%2Fstore%2Fdata%2F025790559_1-eddc974a30fabc00078432a484dea44f&ads=true#file%3A///W%3A/gdoj-o-8ec83091/sql/.tmp/relational-databases/event-classes/blocked-process-report-event-class.html – Roy Latham Oct 18 '21 at 16:34
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – mustaccio Oct 18 '21 at 16:58