1

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?

1 Answer 1

2

antiques

It looks like you copied and pasted a pretty old script to set that Extended Event 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 to 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';
4
  • I appreciate the response. I removed the original session and recreated one using your script. After manually setting up blocking as described above, I still had no blocking data captured in either the "watch live data" feature in management studio or your stored procedure.
    – Aheho
    Sep 25, 2023 at 20:11
  • 1
    @Aheho You may want to check some more basic things then, like if you're causing blocking on the right server, etc. I use this particular set of scripts a lot to capture blocking, and it's working for me on SQL Server 2022 when I test things right now. Sep 25, 2023 at 20:27
  • If I use SQL Server Profiler, I can see blocked process reports resulting from the commands I described above, but I don't see any via the extended events feature.
    – Aheho
    Sep 26, 2023 at 18:03
  • 1
    @Aheho I believe you, but I can’t troubleshoot your issue any further. It could be permissions, or a million other wonky things. What I provided works immediately when I use it both locally and in my regular consulting engagements. Sep 26, 2023 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.