Blocking Alert: A T-SQL Check for Sessions Blocked Too Long

A blocking alert should fire when someone has waited too long, not every time one session waits for another. The check below finds sessions blocked past a threshold and follows each one to the session at the head of the chain.

Blocking is normal until it is not

The phone rings. “The app is frozen.” You look at the server and a few sessions are waiting on locks. Most of the day that is fine, because short blocking is how locking works. The question is how long is too long for your users.

So the alert needs a threshold in milliseconds, and it should look only at lock waits. One caution: wait_time is the current wait, not the age of the whole incident. A request can change waits between two samples.

Here is the live check. It reads sys.dm_exec_requests and returns nothing when nothing has been blocked for a minute. That is the result you want.

DECLARE @ThresholdMs int = 60000;

SELECT session_id, blocking_session_id, wait_type, wait_time, status
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
  AND wait_type LIKE N'LCK[_]%'
  AND wait_time >= @ThresholdMs
ORDER BY session_id;

Practice on a hand-made snapshot

I cannot block a session for a minute inside a single script. So the demo uses a table filled by hand with the kind of rows the DMV would give you. Session 109 has waited 68,821 ms for an exclusive lock held by session 105. Sessions 118 and 120 wait on locks, but for less than a minute. Session 131 has a long wait on a latch, which is not a lock.

DROP FUNCTION IF EXISTS dbo.BlockingHeadsDemo;
DROP TABLE IF EXISTS dbo.BlockingSnapshotDemo;

CREATE TABLE dbo.BlockingSnapshotDemo
(
    session_id int NOT NULL PRIMARY KEY,
    blocking_session_id int NOT NULL,
    wait_type nvarchar(60) NULL,
    wait_time int NOT NULL,
    status nvarchar(30) NOT NULL
);

INSERT dbo.BlockingSnapshotDemo VALUES
(109, 105, N'LCK_M_X',  68821, N'suspended'),
(118, 105, N'LCK_M_U',  15000, N'suspended'),
(120, 118, N'LCK_M_S',  12000, N'suspended'),
(131, 140, N'LATCH_EX', 70000, N'suspended');

DECLARE @ThresholdMs int = 60000;

SELECT session_id, blocking_session_id, wait_type, wait_time, status
FROM dbo.BlockingSnapshotDemo
WHERE blocking_session_id > 0
  AND wait_type LIKE N'LCK[_]%'
  AND wait_time >= @ThresholdMs
ORDER BY session_id;

Walk the chain to the head

A blocked session can also be blocking someone else. The function below starts from each waiter over the threshold and follows blocking_session_id until no further blocker shows up. It keeps a depth counter and stops at 32, so a strange loop cannot run away.

At 60 seconds, session 109 points straight at session 105, with depth 0.

CREATE OR ALTER FUNCTION dbo.BlockingHeadsDemo (@ThresholdMs int)
RETURNS TABLE
AS
RETURN
WITH Chains AS
(
    SELECT r.session_id AS WaitingSession, r.blocking_session_id AS CurrentBlocker, 0 AS Depth
    FROM dbo.BlockingSnapshotDemo AS r
    WHERE r.blocking_session_id > 0 AND r.wait_type LIKE N'LCK[_]%'
      AND r.wait_time >= @ThresholdMs
    UNION ALL
    SELECT c.WaitingSession, r.blocking_session_id, c.Depth + 1
    FROM Chains AS c
    JOIN dbo.BlockingSnapshotDemo AS r ON r.session_id = c.CurrentBlocker
    WHERE r.blocking_session_id > 0 AND c.Depth < 32
),
Ranked AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY WaitingSession ORDER BY Depth DESC) AS rn
    FROM Chains
)
SELECT WaitingSession, CurrentBlocker AS HeadCandidate, Depth
FROM Ranked
WHERE rn = 1;
GO
SELECT WaitingSession, HeadCandidate, Depth
FROM dbo.BlockingHeadsDemo(60000)
ORDER BY WaitingSession;

Now lower the threshold to ten seconds. Session 120 waits on 118, and 118 waits on 105. So the head for 120 is 105, at depth 1.

SELECT WaitingSession, HeadCandidate, Depth
FROM dbo.BlockingHeadsDemo(10000)
ORDER BY WaitingSession;

Find out who the head is

The head is often a session that is just sitting there with an open transaction, so it may not appear in sys.dm_exec_requests at all. Use sys.dm_exec_sessions for login, host, program, and open transaction count.

For the text, take the request’s sql_handle if there is one, and otherwise the connection’s most recent handle. That text is a batch, and not always the statement that took the lock.

The demo needs a real session to look up, so I use my own. I open a transaction, add a waiter that points at my session, run the lookup, and roll back. The open transaction count is 1.

BEGIN TRANSACTION;

INSERT dbo.BlockingSnapshotDemo VALUES (150, @@SPID, N'LCK_M_X', 20000, N'suspended');

SELECT h.WaitingSession, h.HeadCandidate, s.login_name, s.program_name,
       s.open_transaction_count, t.text AS HeadBatchText
FROM dbo.BlockingHeadsDemo(15000) AS h
LEFT JOIN sys.dm_exec_sessions AS s ON s.session_id = h.HeadCandidate
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = h.HeadCandidate
OUTER APPLY (SELECT TOP (1) most_recent_sql_handle
             FROM sys.dm_exec_connections
             WHERE session_id = h.HeadCandidate
             ORDER BY connect_time DESC) AS c
OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) AS t
WHERE h.WaitingSession = 150;

ROLLBACK TRANSACTION;

Do not email every minute

A job that runs every minute will send the same alert sixty times an hour. Store the alert state somewhere and use a cooldown. Notify again when the head blocker changes or the wait gets much worse. Send one message when it clears.

Test the whole path with two windows in a throwaway database, never on a live business table. Check that the job owner has permission to send the mail. Then compare your alerts with the pain users actually felt, and adjust the threshold.

The last block removes the demo function and table.

DROP FUNCTION IF EXISTS dbo.BlockingHeadsDemo;
DROP TABLE IF EXISTS dbo.BlockingSnapshotDemo;

Next time the phone rings, you will know who is at the front of the line.

A blocking alert is not a kill order, it is a nudge to go and look.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.