How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report

Question: How can I monitor SQL Server blocked processes with a SQL Server alert and capture the details for analysis. I’d like the alert to trigger when there is more than one blocked process.

When the blocked process triggers the SQL alert, I want sp_whoisActive to execute , capture details and email the report.

sp_whoisActive is one of the tools I use  when troubleshooting SQL Server performance issues

Read more on sp_WhoIsActive utility for SQL Server troubleshooting

Answer: There are multiple ways of achieving this goal. Monitoring SQL blocked processes is an excellent way of identifying tuning opportunities.

The method I’m proposing includes a) Create a SQL Server Job which will manage the execution of the sp_whoisActive stored procedure

b)Create a SQL alert . The SQL Alert has a performance condition, if the condition is satisfied, than trigger the execution of the SQL Server Agent Job

Step 1 : Create a SQL Server Agent Job. The SQL Server Agent Job will  trigger the sp_whoisActive stored procedure . You’ll need to modify some details:

@recipients

@profile_name

declare @subject varchar(max),
 @cmd varchar(max),
 @file varchar(100)
 select    @subject = @@servername  + ' Encountered Blocking sessions',
 @cmd = 'exec master.dbo.sp_WhoIsActive'

exec msdb.dbo.sp_send_dbmail
 @profile_name = 'Main',
 @recipients = '[email protected]',
 @subject = @subject,
 @body= '',
 @query = @cmd,
 @query_result_separator = '|',
 @query_result_no_padding = 1

Step 2 : Create an SQL alert . A couple things which you can adjust. The @performance condition , accesses a perfmon counter for a specific SQL Server Instance : MSSQL$DBA_TEST. Adjust this reference for your own purposes.

The @job_id is the GUID for the SQL Server Agent Job created in Step 1

USE [msdb]
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Report Blocked Processes',
 @message_id=0,
 @severity=0,
 @enabled=1,
 @delay_between_responses=0,
 @include_event_description_in=0,
 @database_name=N'',
 @notification_message=N'',
 @event_description_keyword=N'',
 @performance_condition=N'MSSQL$DBA_TEST:General Statistics|Processes blocked||>|1',
@job_id=N'FEFBB19E-39AD-4818-9EFF-25457B2F162E'
GO

Read More

 Blocked Process Report and how to read (SQL Server DBA)

SQL Server – Understand Key wait resource in Blocking (SQL ...

SQL Server – Find queries causing Lock Timeouts per second

SQL Server - How to detect and troubleshoot blocking and deadlocks