sp_HumanEventsBlockViewer

What started as a helper/companion script for sp_HumanEvents turned into one of my most-used scripts while consulting.

Its purpose is to analyze the blocked process report Extended Event by parsing out all of the important event data, and rounding up what had the most blocking and other problems.

To get started using it, you’ll need to do a couple things.

First, enable the blocked process report:

EXEC sys.sp_configure
    N'show advanced options',
    1;
RECONFIGURE;
GO

EXEC sys.sp_configure
    N'blocked process threshold',
    5; --Seconds
RECONFIGURE;
GO

The second command may need some attention, if you don’t care about blocking that happens for 5 seconds. You can set that number higher if you want, but that’s as low as it goes.

Second, you need to set up an Extended Event to capture the output of the blocked process report. If you’re using Azure SQL DB or Azure Managed Instance, you’ll have to change things a bit.

What the changes may be by the time you read this, I don’t want to speculate about.

Here’s the normal version:

CREATE EVENT SESSION 
    blocked_process_report
ON SERVER
    ADD EVENT 
        sqlserver.blocked_process_report
    ADD TARGET 
        package0.event_file
    (
        SET filename = N'bpr'
    )
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);

Once that’s up and collecting data for a bit, you can use sp_HumanEventsBlockViewer to pull data from it like this:

EXEC dbo.sp_HumanEventsBlockViewer
    @session_name = N'blocked_process_report';

To learn more about the parameters and using the script, run it with @help = 1 for a full rundown.

If you need help with interpreting the results, start here:

Good luck out there!