Skip to content

Should sp_HumanEventsBlockViewer work with system_health when the Blocked Process Report is not enabled? #617

@ReeceGoding

Description

@ReeceGoding

Is your feature request related to a problem? Please describe.
I'm unsure if this is a feature request or a bug, but sp_HumanEventsBlockViewer has puzzled me. It clearly has logic that checks if it's parsing the puny version of the Blocked Process Report that exists in the system_health Extended Event

    @is_system_health =
        CASE
            WHEN @session_name LIKE N'system%health'
            THEN 1
            ELSE 0
        END,

and I'm sure that I saw a 2023 video of Erik's earlier this week where he mentioned parsing said puny report.

I'm puzzled because only people who have the Blocked Process Report disabled would bother to check system_health's lesser version. Yet, sp_HumanEventsBlockViewer errors out immediately if you have the Blocked Process Report disabled.

/*Check if the blocked process report is on at all*/
IF EXISTS
(
    SELECT
        1/0
    FROM sys.configurations AS c
    WHERE c.name = N'blocked process threshold (s)'
    AND   CONVERT(int, c.value_in_use) = 0
)
BEGIN
    RAISERROR(N'The blocked process report needs to be enabled:
EXECUTE sys.sp_configure ''show advanced options'', 1;
EXECUTE sys.sp_configure ''blocked process threshold'', 5; /* Seconds of blocking before a report is generated */
RECONFIGURE;',
    11, 0) WITH NOWAIT;
    RETURN;
END;

So why do we have special logic for system_health if nobody who would use it can?

Describe the solution you'd like
Make sp_HumanEventsBlockViewer able to parse system_health's lesser version of the Blocked Process Report if you have the Blocked Process Report turned off.

Describe alternatives you've considered
Document why only a fool would follow my path. Despite the special cases in the code, I don't think that any of the documentation for sp_HumanEventsBlockViewer mentioned how it interacts with system_health.

Are you ready to build the code for the feature?
I'm going to play around with it tonight, but I think there's an 80% chance that Erik will explain why what I'm trying to do is stupid. My lack of experience with the Blocked Process Report is embarrassing. I don't think that I've ever had it enabled on a production box.

IMPORTANT: If you're going to contribute code, please read the contributing guide first.
https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md
Might do.

Metadata

Metadata

Assignees

Labels

bugSomething isn't workingsp_HumanEventsBlockViewerParse the SQL Server blocked process report

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions