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!
