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
Can someone please share your ideas?
Many thanks.
CodePudding user response：
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 Mallon's 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))
;
CodePudding user response：
I just rewrite for myself and I get below results with no error but I'm not sure this really have return result when must have result.
DECLARE @SESSION int = 53;
WITH cte
AS (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) AS is_null
, R3.session_id
FROM sys.dm_exec_sessions R3
WHERE R3.session_id = @SESSION)
SELECT is_null
, MAX(R.session_id)
FROM sys.dm_exec_requests R
JOIN cte ON cte.session_id = R.session_id
WHERE R.blocking_session_id = @SESSION
GROUP BY cte.is_null;