Blocked processes are often a big problem for DBAs, because they’re difficult to monitor. We receive desperate calls saying “Everything is slow!” but before we can do anything a second call arrives saying, “Don’t worry, everything’s OK again!”

Most of the time, this happens because a badly-behaved process blocks other tasks for just a short period. So how do you find this kind of problem?

SQL Server has a server configuration called Blocked Process Threshold. You can set a value, in milliseconds, and SQL Server will generate a report every time a process is blocked by this amount of time.

To configure Blocked Process Threshold, you can use this code

EXEC sp_configure ‘Blocked Process Threshold’, 5

RECONFIGURE

GO

In this example, one blocked process report will be generated every time a process is blocked for more than five seconds. You’ll need to adapt this value to your servers.

There are a few ways to capture blocked process reports:

• SQL Profiler has an event called ‘Blocked Process Report’ specifically to capture this information.
• Extended Events also contains a ‘Blocked Process Report’ event.
• Alerts – it’s possible to generate an alert around blocked process reports and send an email in response.
• Third-party tools, such as Redgate SQL Monitor.

Here’s the code to create an extended events session to capture the blocked process report:

— Create the session

CREATE EVENT SESSION [Blocked] ON SERVER 

ADD EVENT sqlserver.blocked_process_report 

ADD TARGET package0.event_file

(SET filename=N’C:\xel\blocked.xel’)      — You need to change the path or create the folder

GO 

— Start the session

ALTER EVENT SESSION [Blocked]ON SERVERSTATE = start;

GO

After creating and starting the session, you can query the reports captured by this session. You need to use the sys.fn_xe_file_target_read_file Dynamic Management Function (DMF)  to read the session information.

The main information is returned as an XML field, so you’ll need to extract the information from the XML using XML functions.

Here’s the query to retrieve the report:

SELECT

theNodes.event_data.value   (‘(//blocked-process/process)[1]/@spid’,   ‘int’) AS blocking_process 

,theNodes.event_data.value   (‘(//blocked-process/process/inputbuf)[1]’,    ‘varchar(max)’) AS blocking_text 

,theNodes.event_data.value   (‘(//blocked-process/process)[1]/@clientapp’,    ‘varchar(100)’) AS blocking_app ,theNodes.event_data.value   (‘(//blocked-process/process)[1]/@loginname’,    ‘varchar(50)’) AS blocking_login ,theNodes.event_data.value   (‘(//blocked-process/process)[1]/@isolationlevel’    ‘varchar(50)’) AS blocking_isolation ,theNodes.event_data.value   (‘(//blocked-process/process)[1]/@hostname’,    ‘varchar(50)’) AS blocking_host ,theNodes.event_data.value   (‘(//blocking-process/process)[1]/@spid’,    ‘int’) AS blocked_process ,theNodes.event_data.value   (‘(//blocking-process/process/inputbuf)[1]’,    ‘varchar(max)’) AS blocked_text ,theNodes.event_data.value   (‘(//blocking-process/process)[1]/@clientapp’,    ‘varchar(100)’) AS blocked_app ,theNodes.event_data.value   (‘(//blocking-process/process)[1]/@loginname’,    ‘varchar(50)’) AS blocked_login ,theNodes.event_data.value   (‘(//blocked-process/process)[1]/@isolationlevel’,    ‘varchar(50)’) AS blocked_isolation ,theNodes.event_data.value   (‘(//blocking-process/process)[1]/@hostname’,    ‘varchar(50)’) AS blocked_host 

  FROM ( SELECT CONVERT(XML, event_data) event_data   FROM   sys.fn_xe_file_target_read_file(‘c:\xel\blocked*.xel’,                                            NULL, NULL, NULL) ) theData CROSS APPLY theData.event_data.nodes       (‘//event’) theNodes ( event_data )

Extended Events doesn’t include a way to get notifications about the reports, but you can get them using an alert.

SQL Server has a performance counter called Process Blocked that counts the number of blocked processes according to your Blocked Process Threshold configuration. You can create an alert over this counter and configure the notification to send an email. Here’s the script:

— Create the alert

EXEC msdb.dbo.sp_add_alert

@name =      N’Blocked Process Alert’,

@enabled = 1,     

@category_name = N'[Uncategorized]’,     

@performance_condition =            N’General Statistics|Processes blocked||>|0′

GO

— Add one e-mail notification to one operator

EXEC msdb.dbo.sp_add_notification

@alert_name =      N’Blocked Process Alert’,       

@operator_name = N’Fulano’               — You need to configure the operator first       ,

@notification_method = 1;

GO

And Big Thanks To Red Gate..(NO NO i dont work for RED GATE or nor they sponsor me but they send this kind of mail very often )