2

I have been using sp_whoisactive and this is very helpful in finding details of running sessions and blocking.

I wanted to check whether there is any option to schedule a job based on this procedure and alert me only when there is active blocking on database and blocking is held for more than configurable number of minutes.

Also, alert should be triggered only when it reaches more than configurable number of blocking on database.

I used the details given at http://www.sqlserver-dba.com/2017/01/how-to-monitor-blocked-processes-with-sql-alert-and-email-sp_whoisactive-report.html however it sends alert on every run and doesn't seem to check regarding blocking. Sends email whether there is only one query running or multiple queries running, whether it is with blocking or without blocking. Not sure, if I am missing anything.SQL Job For Blocking

1
  • I get email on each run and can see no blocking is there in the mentioned queries, I meant blocking_session_id is NULL and also, number of queries are less than the value set in "Alert if counter" as shown in screenshot. I am also monitoring details from Solarwinds DPA and don't find any DB blocking. I wanted alert to be triggered only on active blocking i.e. blocking_session_id has spid and not NULL. Commented May 31, 2018 at 10:09

1 Answer 1

7

The script you linked to uses SQL Server's built-in Blocked Process Report. It doesn't have any configuration options about the level of blocking - it simply wakes up on an interval (say, every 5 seconds), checks for blocking, and if it finds any, runs the script.

There is a Blocked Process Threshold setting, but that just determines the interval at which the Blocked Process Report runs - like every 5 seconds. (Don't go lower than that.)

If you want to build your own custom Blocked Process Report and put logic in there about how much blocking is occurring, and in which databases, that would be an exercise left for the reader. You can start with Darko's Building a Custom Blocked Process Report.

From my own experience over the years, I've seen people who wanted to build these kinds of reports, and then the very next thing they do is build an Outlook rule to move all of these alerts into a folder, and they read 'em later. If you're going to do that, don't bother sending emails.

Just set up an Extended Events session to look for blocked processes and deadlocks - note that you'll need to change the file locations & names for your server:

CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\temp\XEventSessions\blocked_process.xel',
     metadatafile = N'c:\temp\XEventSessions\blocked_process.xem',
     max_file_size=(65536),
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

Enable SQL Server's Blocked Process Report:

EXEC sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE ;
GO
/* Enabled the blocked process report */
EXEC sp_configure 'blocked process threshold', '5';
RECONFIGURE
GO
/* Start the Extended Events session */
ALTER EVENT SESSION [blocked_process] ON SERVER
STATE = START;

And when you want to check for blocking, this query reads the files:

WITH events_cte AS (
  SELECT
    xevents.event_data,
    DATEADD(mi,
    DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
    xevents.event_data.value(
      '(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
    xevents.event_data.value(
      '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
      AS [client app name],
    xevents.event_data.value(
      '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
      AS [client host name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)')
      AS [database name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int')
      AS [database_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int')
      AS [object_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int')
      AS [index_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000
      AS [duration (ms)],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar')
      AS [lock_mode],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int')
      AS [login_sid],
    xevents.event_data.query(
      '(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]')
      AS blocked_process_report,
    xevents.event_data.query(
      '(event/data[@name="xml_report"]/value/deadlock)[1]')
      AS deadlock_graph
  FROM    sys.fn_xe_file_target_read_file
    ('C:\temp\XEventSessions\blocked_process*.xel',
     'C:\temp\XEventSessions\blocked_process*.xem',
     null, null)
    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
)
SELECT
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN 'Deadlock'
       ELSE 'Blocked Process'
       END AS ReportType,
  [event time],
  CASE [client app name] WHEN '' THEN ' -- N/A -- '
                         ELSE [client app name]
                         END AS [client app _name],
  CASE [client host name] WHEN '' THEN ' -- N/A -- '
                          ELSE [client host name]
                          END AS [client host name],
  [database name],
  COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
  COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
  index_id,
  [duration (ms)],
  lock_mode,
  COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN deadlock_graph
       ELSE blocked_process_report
       END AS Report
FROM events_cte
ORDER BY [event time] DESC ;

Hope that helps get you started. Also, just FYI, I edited your question to remove the part about sp_WhoIsActive being mine - it's not. It's Adam Machanic's. If you ever meet him in person, you should say thank you to him and buy him his drink of choice.

Not the answer you're looking for? Browse other questions tagged or ask your own question.