我正在尝试让 CTE 计算服务器上的 blocking 会话。目前我做了一个函数来这样做（返回 blocking 会话数量）。但仍然无法用 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有人可以分享你的想法吗？
非常感谢。
回答1
你写的递归函数调用对我来说看起来很可怕。如果你有很多 blocking 的场景，我预计这会很慢，并且会返回最少的信息。它会告诉您有多少会话被阻止，但没有其他有用的信息。
我写过关于https://am2.co/2017/10/finding-leader-blocker/，并在我的https://am2.co/dbadb中发布了https://github.com/amtwo/dba-database/blob/production/stored-procedures/dbo.Check_Blocking.sql。
完整的过程是几百行代码，所以我不会在这里重新发布整个内联......但一般算法是：
- 获取 Blocking 中涉及的所有会话（拦截器和服务员）
- 做一些事情来收集输入缓冲区和其他元数据
- 确定主要的阻碍因素
- 识别出那些领先的阻塞者背后的 blocking 链。
这是该代码的精简版本（删除了一些会话元数据），您可以复制/粘贴/运行它，但我建议您查看完整版本以更好地了解 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))
;回答2
我只是为自己重写，我得到以下结果而没有错误，但我不确定这在必须有结果时是否真的有返回结果。
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;