Capturing Blocked Process Report via Extended Events

Question

I ran the following commands to setup extended event captures for blocked processes:

CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\temp\XEventSessions\blocked_process.xel',
     metadatafile = N'c:\temp\XEventSessions\blocked_process.xem',
     max_file_size=(65536),
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

I also ran the following to set the blocked process report threshold to 5 seconds:

EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
/* Enabled the blocked process report */
EXEC sp_configure 'blocked process threshold', '5';
RECONFIGURE
GO
/* Start the Extended Events session */
ALTER EVENT SESSION [blocked_process] ON SERVER
STATE = START;

Using SQL studio manager's UI, I started the blocked_process extended event capture.

In order to test the setup, I explicitly started a transaction in one query window, edited a row, and attempted to edit the same row in another window.

The second window wouldn't complete until I ran "commit transaction" in the first window.

Despite the fact that I waited well over the 5 second blocking threshold, the blocked_process extended event capture didn't capture anything. Nor did it capture any blocking that occurred within our application, which it should have because I know there are blocking issues.

We are running sql server 2022 enterprise edition.

Is there some other configuration option I'm missing that may explain why I'm not capturing anything with my extended event session?

Answer 1

antiques

It looks like you copied and pasted a pretty old script to set that Extended Events session up. Extended Events haven't required a metadata file since 2008 or 2012, and the asynchronous file target hasn't been a thing in about just as long.

I'd suggest using a session a bit closer to this one. You are free to edit it to capture additional information. I'd also suggest having separate sessions for blocking and deadlocks so the files don't become overloaded with competing information.

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
);
ALTER EVENT SESSION
    blocked_process_report
ON SERVER 
    STATE = START;
GO

When you're using the GUI, make sure that you're either using the Watch Live Data option if you're expecting to see current events make it into the display. If you're viewing the event file contents using View Target Data, you may need to close and reopen to see new events.

Alternately, you could use my stored procedure sp_HumanEventsBlockViewer to break down the XML for you, and do some analysis on the results.

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