生產環境出現死鎖或嚴重阻塞時，需要在最短時間內找出「誰阻塞了誰」以及「執行什麼 SQL」，以便決定是否 Kill 掉頭部進程。
USE master;
GO
DECLARE @spid INT, @bl INT;
DECLARE s_cur CURSOR FOR
SELECT 0, blocked
FROM (SELECT * FROM sys.sysprocesses WHERE blocked > 0) a
WHERE NOT EXISTS (
SELECT * FROM (SELECT * FROM sys.sysprocesses WHERE blocked > 0) b
WHERE a.blocked = b.spid
)
UNION
SELECT spid, blocked FROM sys.sysprocesses WHERE blocked > 0;
OPEN s_cur;
FETCH NEXT FROM s_cur INTO @spid, @bl;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @spid = 0
PRINT N'引發死鎖/阻塞的頭部進程 SPID: ' + CAST(@bl AS VARCHAR(10)) + N'，其執行 SQL 如下：';
ELSE
PRINT N'進程 SPID: ' + CAST(@spid AS VARCHAR(10)) + N' 被 SPID: ' + CAST(@bl AS VARCHAR(10)) + N' 阻塞，當前 SQL 如下：';
DBCC INPUTBUFFER(@bl); -- 顯示頭部進程最後一批次 RPC/語句
FETCH NEXT FROM s_cur INTO @spid, @bl;
END
CLOSE s_cur; DEALLOCATE s_cur;
輸出說明：第一行列印頭部阻塞者 SPID，後續列印被阻塞鏈路。DBCC INPUTBUFFER 只回傳最後一批次文字，若需完整批次可改用 sys.dm_exec_input_buffer (2016+)。
CREATE PROCEDURE dbo.sp_LockInfo
@kill_lock_spid BIT = 1, -- 1=自動 Kill 死鎖頭部進程
@show_spid_if_nolock BIT = 1 -- 1=無死鎖時顯示所有進程
AS
BEGIN
SET NOCOUNT ON;
-- 1. 收集死鎖鏈路進程
SELECT
ID = IDENTITY(INT,1,1),
Flag = CASE WHEN a.blocked = 0 THEN N'死鎖頭部進程' ELSE N'|_受害者_>' END,
SPID = a.spid, KPID = a.kpid, Blocked = a.blocked,
DBID = a.dbid, DBName = DB_NAME(a.dbid),
LoginName = a.loginame, CPU = a.cpu, LoginTime = a.login_time,
OpenTran = a.open_tran, Status = a.status,
HostName = a.hostname, Program = a.program_name,
HostProcess = a.hostprocess, NTDomain = a.nt_domain, NetAddress = a.net_address,
Sort1 = a.spid, Sort2 = CASE WHEN a.blocked = 0 THEN 0 ELSE 1 END
INTO #t
FROM sys.sysprocesses a
JOIN (SELECT blocked FROM sys.sysprocesses GROUP BY blocked) b ON a.spid = b.blocked
WHERE a.blocked = 0
UNION ALL
SELECT
N'|_受害者_>', a.spid, a.kpid, a.blocked, a.dbid, DB_NAME(a.dbid),
a.loginame, a.cpu, a.login_time, a.open_tran, a.status,
a.hostname, a.program_name, a.hostprocess, a.nt_domain, a.net_address,
a.blocked, 1
FROM sys.sysprocesses a WHERE a.blocked <> 0
ORDER BY Sort1, Sort2;
DECLARE @cnt INT = @@ROWCOUNT, @i INT = 1;
-- 2. 無死鎖時可選顯示正常進程
IF @cnt = 0 AND @show_spid_if_nolock = 1
BEGIN
INSERT #t (Flag, SPID, KPID, Blocked, DBID, DBName, LoginName, CPU, LoginTime, OpenTran, Status, HostName, Program, HostProcess, NTDomain, NetAddress)
SELECT N'正常進程', spid, kpid, blocked, dbid, DB_NAME(dbid), loginame, cpu, login_time, open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address
FROM sys.sysprocesses;
SET @cnt = @@ROWCOUNT;
END
-- 3. 取得各進程輸入緩衝區 SQL
IF @cnt > 0
BEGIN
CREATE TABLE #t1 (ID INT IDENTITY(1,1), EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(MAX));
IF @kill_lock_spid = 1
BEGIN
DECLARE @spid VARCHAR(10), @flag NVARCHAR(20);
WHILE @i <= @cnt
BEGIN
SELECT @spid = CAST(SPID AS VARCHAR(10)), @flag = Flag FROM #t WHERE ID = @i;
INSERT #t1 EXEC ('DBCC INPUTBUFFER(' + @spid + ')');
IF @flag = N'死鎖頭部進程' EXEC ('KILL ' + @spid);
SET @i += 1;
END
END
ELSE
BEGIN
WHILE @i <= @cnt
BEGIN
INSERT #t1 EXEC ('DBCC INPUTBUFFER(' + (SELECT CAST(SPID AS VARCHAR) FROM #t WHERE ID = @i) + ')');
SET @i += 1;
END
END
-- 4. 輸出結果
SELECT t.*, SQL_Text = t1.EventInfo
FROM #t t JOIN #t1 t1 ON t.ID = t1.ID;
END
END
GO
-- 僅顯示，不自動 Kill
EXEC dbo.sp_LockInfo @kill_lock_spid = 0, @show_spid_if_nolock = 1;
-- 發現死鎖頭部自動 Kill (謹慎使用！)
EXEC dbo.sp_LockInfo @kill_lock_spid = 1, @show_spid_if_nolock = 0;
建議改用擴充事件 或 sys.dm_exec_requests / sys.dm_os_waiting_tasks 組合查詢，避免 DBCC INPUTBUFFER 的 4000 字元截斷限制：
SELECT
r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, r.wait_resource,
t.text AS CurrentSQL, qp.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.blocking_session_id > 0 OR r.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > 0);
掌握「頭部阻塞者 → 受害者鏈路 → 當前 SQL」的快速定位流程，配合現代 DMV 查詢，能在死鎖風暴中精準斷路、快速恢復服務。預存程序版本適合納入監控工具箱，一鍵診斷。
