SQL SERVER - Setup Blocked Process Report in Extended Events

In this blog post, I will demonstrate how to setup a Blocked Process Report using Extended Events.

1. In Management Studio under Extended Events, right-click Sessions then choose New Session Wizard.

2. Click Next.

3. Input Session name. Click Next.

4. Choose “Do not use a template“. Click Next.

5. Search for “blocked” in the Event Library. Select blocked_process_report. Click Next.

6. Check database_name and also sql_text (optional because these values will show in the XML report). Click Next.

7. Click Close.

8. Set the blocked process threshold. In this case I have set it to 10 seconds.

sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 10 ;  
GO  
RECONFIGURE ;  
GO

9. Start Extended Events.

ALTER EVENT SESSION [Blocked Process Report] 
ON SERVER STATE = START

Cheers!

SQL SERVER – Setup Blocked Process Report in Extended Events