[BUG] Not clear how to work with blocking monitor

[TrudAX]

Component

Lite

Performance Monitor Version

latest nightly build

SQL Server Version

Azure SQL

Windows Version

Windows server 2022

Describe the Bug

Not clear how Blocked process report should work

I see this picture

Hit Refresh

When I press Right Click - View plan, the following error occurs

When I press Get Actual plan, this error

When I press Save on the line, it saves the XML like this (SQL truncated):

Steps to Reproduce

Select Blocking-Blocked process reports and press Refresh

Expected Behavior

Expected behaviour:
There should be some functions that allow:

1. See the blocking/blocked SQL text (without SQL text truncate)
2. See plans for these blocking

Actual Behavior

Text is truncating, and plan selection generates an error. (maybe it is related to #857, but blocking should not require master DB access)

Error Messages / Log Output

Screenshots

No response

Additional Context

No response

[erikdarlingdata]

Thanks for the detailed repro. Dug into this — here's what's going on and what we're going to fix:

1. "View Plan" on a blocked process row — the context menu is shared across grids, but the handler has no case for blocked-process rows, so it silently falls through to "no plan available." What makes this harder than other grids: the blocked process report XML doesn't give us query_hash, only sql_handle (and sometimes stmt_start/stmt_end offsets inside <executionStack><frame>). We'll parse those out at collection time and add a plan lookup path that goes sql_handle → sys.dm_exec_query_plan(plan_handle). If the plan has since been evicted from cache, you'll get a clear "no longer in cache" message instead.

2. "Get Actual Plan" — you're right, we shouldn't offer this on blocked process rows at all. Re-executing a query that was mid-transaction holding locks is a foot-gun. Removing the menu item from this grid.

3. Truncated SQL text in the saved XML — that truncation is SQL Server's own behavior on the blocked_process_report extended event: the <inputbuf> element is capped (usually 4000 chars). The tool is saving exactly what SQL Server emitted. Nothing we can do about that from the collector side. Once (1) lands, you'll at least be able to get to the plan, which is usually what you actually want.

4. Re: #857 / master DB access — blocking collection reads from the ring buffer target via sys.fn_xe_file_target_read_file and DMVs; it shouldn't need master. That's a separate fix being tracked on #857.

Opening a PR for (1) and (2) shortly.

[TrudAX]

Great, thanks
But what is the idea of this Blocked Process Reports Tab? I initially thought it was currently blocked processes, but it seems it showed processes that had been blocked for the last 4h.
I also get an alert about a large number of blocking processes, even though no one exists now(the alert probably should work only if active blocking exists)
Do you plan to add something like "Active blocked processes" (processes that are currently blocked), that maybe display some ?

[erikdarlingdata]

@TrudAX No, this is historical blocking. I do not plan to add current blocking as its own specific section. That's more the idea behind the live queries tabs. The alerts show up until you acknowledge them.

[TrudAX]

Thanks. But then the usage scenario is not clear. some flag e.g. "session is active" may help
So, a typical user request: "System is slow, can you check if there is any blocking?"
In a graph, I see the current number of blocking, in a Grid - a log of records for the last 4h
If the number of current blocking is not 0, I have no idea whether queries from the Grid are still active or not. The text is also truncated it is not usable for future analysis

[erikdarlingdata]

@TrudAX Yes, the text is truncated in the blocked process report. It only stores so much. That is not my doing. I'm going to make a reasonable effort here to use the sql handle and statement offsets to correlate queries in the blocked process report to other collectors, but I can't promise it will be 100% successful.

Monitoring like this is not always the strongest tool for answering "what's slow now?" type questions, though I've done my best to make it reasonably point in time without spamming the server. That's also why the active queries tab is on demand.

[TrudAX]

ok. clear.
But maybe just add some settings to save the full query after some threathhold (it will require additional select, not sure how this fit the monitoring idea, but probably for queries like more than 60-300 seconds in blocking it may be fine)
active queries is still not working for me, hope it can be fixed

[erikdarlingdata]

@TrudAX Again, the text comes from the blocked process report. I am not truncating it. I'm working on something to allow you to correlate statements in the blocked process report to other views at the moment.

Would you like to contribute code that does what you're after here?

[TrudAX]

@erikdarlingdata - yes, I understand that you are not truncating it. It currently displays the output from SQL that is truncated by SQL. But for Dynamics all queries are large and do not fit into the default buffer

Not with a code, definitely; it looks like a big task to set all this up. But I can help with testing
thank you for the support