Recursive CTE in SQL Server to count blocking sessions

May 18, 0202 Andy Stack Overflow 0

clip art of a double-quote character

Question

I’m trying to make CTE to count blocking sessions on server. Currently I made a function to do so(returns blocking session amount). But still could not write this query with CTE.

    CREATE FUNCTION dbo.SESSIONS(@SESSION int)
    RETURNS INT
    AS 
    BEGIN
        DECLARE  @returnValue int =0
        SET @returnValue = @returnValue + (			
    			SELECT isNULL((
    			SELECT (SELECT COUNT(session_id) FROM sys.dm_exec_requests R2 WHERE blocking_session_id =R1.blocking_session_id) AS [BLOCKING]
    			FROM sys.dm_exec_requests r1 
                WHERE blocking_session_id = R3.session_id
    			GROUP BY blocking_session_id), 0)
    			FROM sys.dm_exec_sessions R3 where session_id=@SESSION)
    
    	DECLARE @s int = (SELECT MAX(session_id) FROM  sys.dm_exec_requests
             			  WHERE blocking_session_id = @SESSION)

    	IF @s IS NOT NULL
    	BEGIN
    		SET @returnValue = @returnValue + dbo.SESSIONS(@s)
    	END
    
    	RETURN @returnValue
    END

asked 2022-05-18 by mortu

Answer

The recursive function call you’ve written looks pretty scary to me. If you have a scenario where there is a lot of blocking, I expect this will be pretty slow, and will return minimal information. It’ll tell you how many sessions are blocked, but no additional helpful info.

I’ve written about finding the leading blocker, and published a stored procedure to do so in my open source DBA database.

The full procedure is a couple hundred lines of code, so I won’t re-publish the whole thing inline here… but the general algorithm is:

  • Grab all sessions involved in Blocking (both blockers & waiters)
  • Do some stuff to collect input buffers & other metadata
  • Identify the *leading* blockers
  • Identify the blocking chain held up behind those leading blockers.

Here’s a trimmed down version of that code (with some of the session metadata removed), which you can copy/paste/run, though I’d recommend checking out the full version for a better look what is happening with blocking:

DROP TABLE IF EXISTS #Blocked, #LeadingBlocker;
/*************************************************************************************************
    This code is licensed as part of Andy Mallons DBA Database.
    https://github.com/amtwo/dba-database/blob/master/LICENSE
    ©2014-2020 ● Andy Mallon ● am2.co
*************************************************************************************************/
DECLARE
    @BlockingDurationThreshold smallint = 5,
    @BlockedSessionThreshold smallint = NULL

SET NOCOUNT ON;
--READ UNCOMMITTED, since we're dealing with blocking, we don't want to make things worse.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

---Sure, it would work if you supplied both, but the ANDing of those gets confusing to people, so easier to just do this.
IF ((@BlockingDurationThreshold IS NOT NULL AND @BlockedSessionThreshold IS NOT NULL)
    OR COALESCE(@BlockingDurationThreshold, @BlockedSessionThreshold) IS NULL)
BEGIN
    RAISERROR('Must supply either @BlockingDurationThreshold or @BlockedSessionThreshold (but not both).',16,1);
END;

DECLARE @Id int = 1,
        @Spid int = 0,
        @DbName nvarchar(256),
        @ObjectName nvarchar(256),
        @IndexName nvarchar(256),
        @Sql nvarchar(max);

CREATE TABLE #Blocked (
    ID int identity(1,1) PRIMARY KEY,
    WaitingSpid smallint,
    BlockingSpid smallint,
    LeadingBlocker smallint,
    BlockingChain nvarchar(4000),
    DbName sysname,
    HostName nvarchar(128),
    ProgramName nvarchar(128),
    LoginName nvarchar(128),
    LoginTime datetime2(3),
    LastRequestStart datetime2(3),
    LastRequestEnd datetime2(3),
    TransactionCnt int,
    );

CREATE TABLE #LeadingBlocker (
    Id int identity(1,1) PRIMARY KEY,
    LeadingBlocker smallint,
    BlockedSpidCount int,
    DbName sysname,
    HostName nvarchar(128),
    ProgramName nvarchar(128),
    LoginName nvarchar(128),
    LoginTime datetime2(3),
    LastRequestStart datetime2(3),
    LastRequestEnd datetime2(3),
    TransactionCnt int,
    Command nvarchar(32),
    WaitTime int,
    WaitResource nvarchar(256),
    WaitDescription nvarchar(1000),
    SqlText nvarchar(max),
    SqlStatement nvarchar(max),
    InputBuffer nvarchar(4000),
    SessionInfo xml,
    );


--Grab all sessions involved in Blocking (both blockers & waiters)

INSERT INTO #Blocked (WaitingSpid, BlockingSpid, DbName, HostName, ProgramName, LoginName, LoginTime, LastRequestStart, 
                    LastRequestEnd, TransactionCnt)
-- WAITERS
SELECT s.session_id AS WaitingSpid, 
       r.blocking_session_id AS BlockingSpid,
       db_name(r.database_id) AS DbName,
       s.host_name AS HostName,
       s.program_name AS ProgramName,
       s.login_name AS LoginName,
       s.login_time AS LoginTime,
       s.last_request_start_time AS LastRequestStart,
       s.last_request_end_time AS LastRequestEnd,
       -- Need to use sysprocesses for now until we're fully on 2012/2014
       (SELECT TOP 1 sp.open_tran FROM master.sys.sysprocesses sp WHERE sp.spid = s.session_id) AS TransactionCnt
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
WHERE r.blocking_session_id <> 0                --Blocked
AND r.wait_time >= COALESCE(@BlockingDurationThreshold,0)*1000
UNION 
-- BLOCKERS
SELECT s.session_id AS WaitingSpid, 
       COALESCE(r.blocking_session_id,0) AS BlockingSpid,
       COALESCE(db_name(r.database_id),'') AS DbName,
       s.host_name AS HostName,
       s.program_name AS ProgramName,
       s.login_name AS LoginName,
       s.login_time AS LoginTime,
       s.last_request_start_time AS LastRequestStart,
       s.last_request_end_time AS LastRequestEnd,
       -- Need to use sysprocesses for now until we're fully on 2012/2014
       (SELECT TOP 1 sp.open_tran FROM master.sys.sysprocesses sp WHERE sp.spid = s.session_id) AS TransactionCnt
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
WHERE s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests ) --Blockers
AND COALESCE(r.blocking_session_id,0) = 0;                  --Not blocked

--Move the LEADING blockers out to their own table.
INSERT INTO #LeadingBlocker (LeadingBlocker, DbName, HostName, ProgramName, LoginName,
                            LoginTime, LastRequestStart, LastRequestEnd, TransactionCnt)
SELECT WaitingSpid, DbName, HostName, ProgramName, LoginName, 
        LoginTime, LastRequestStart, LastRequestEnd, TransactionCnt
FROM #Blocked b
WHERE BlockingSpid = 0
AND EXISTS (SELECT 1 FROM #Blocked b1 WHERE b1.BlockingSpid = b.WaitingSpid);

DELETE FROM #Blocked WHERE BlockingSpid = 0;

--Update #Blocked to include LeadingBlocker & BlockingChain
WITH BlockingChain AS (
    SELECT LeadingBlocker AS Spid, 
           CAST(0 AS smallint) AS Blocker,
           CAST(LeadingBlocker AS nvarchar(4000)) AS BlockingChain, 
           LeadingBlocker AS LeadingBlocker
    FROM #LeadingBlocker
    UNION ALL
    SELECT b.WaitingSpid AS Spid, 
           b.BlockingSpid AS Blocker,
           RIGHT((CAST(b.WaitingSpid AS nvarchar(10)) + N' ' + CHAR(187) + N' ' + bc.BlockingChain),4000) AS BlockingChain,
           bc.LeadingBlocker
    FROM #Blocked b
    JOIN BlockingChain bc ON bc.Spid = b.BlockingSpid
    )
UPDATE b
SET LeadingBlocker = bc.LeadingBlocker,
    BlockingChain = bc.BlockingChain
FROM #Blocked b
JOIN BlockingChain bc ON b.WaitingSpid = bc.Spid;

-- Populate BlockedSpidCount for #LeadingBlocker
UPDATE lb
SET BlockedSpidCount = cnt.BlockedSpidCount
FROM #LeadingBlocker lb
JOIN (SELECT LeadingBlocker, COUNT(*) BlockedSpidCount FROM #Blocked GROUP BY LeadingBlocker) cnt 
        ON cnt.LeadingBlocker = lb.LeadingBlocker;

--output results
SELECT * FROM #LeadingBlocker 
WHERE BlockedSpidCount >= COALESCE(@BlockedSessionThreshold,BlockedSpidCount)
ORDER BY LoginTime;
--
SELECT * FROM #Blocked b
WHERE EXISTS (SELECT 1 FROM #LeadingBlocker lb 
                WHERE lb.LeadingBlocker = b.LeadingBlocker
                AND lb.BlockedSpidCount >= COALESCE(@BlockedSessionThreshold,lb.BlockedSpidCount))
                ;

answered 2022-05-18 by Andy Mallon