Blocking and Blocked Processes - SQL Server Query Performance Tuning - page 422

Extended Events and the blocked_process_report Event
Extended Events provide an event called blocked_process_report . This event works off the blocked process
threshold that you need to provide to the system configuration. This script sets the threshold to five seconds:
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure
'blocked process threshold',
5;
RECONFIGURE;
This would normally be a very low value in most systems. If you have an established performance service level
agreement (SLA), you could use that as the threshold. Once the value is set, you can configure alerts so that e-mails,
tweets, or instant messages are sent if any process is blocked longer than the value you set. It also acts as a trigger for
the extended event.
To set up a session that captures the blocked_process_report , first open the Extended Events session properties
window. (Although you should use scripts to set up this event in a production environment, I'll show how to use the
GUI.) Provide the session with a name and then navigate to the Events page. Type block into the Event library text
box, which will find the blocked_process_report event. Select that event by clicking the right arrow. You should see
something similar to Figure 20-15 .
Figure 20-15. The blocked process report event selected in the Extended Events window