您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 進程阻塞偵測與解除：sp_who_lock / sp_Kill_lockProcess 完整實作

編輯：關於SqlServer

阻塞是 SQL Server 常見效能殺手。本文提供兩個即用型預存程序：sp_who_lock (遞迴列出完整阻塞鏈並顯示每層執行 SQL) 與 sp_Kill_lockProcess (自動 KILL 阻塞源頭)，並附贈 syslockinfo 進階鎖資訊查詢腳本，幫助 DBA 秒級定位問題。

一、sp_who_lock：阻塞鏈完整可視化

CREATE PROC sp_who_lock AS
BEGIN
  DECLARE @spid INT, @bl INT, @cnt INT, @i INT = 1;
  CREATE TABLE #tmp (id INT IDENTITY(1,1), spid SMALLINT, bl SMALLINT);

  -- 找出阻塞鏈頭 (blocked>0 且無人阻塞它) + 所有被阻塞進程
  INSERT #tmp (spid, bl)
  SELECT 0, blocked FROM (SELECT * FROM sysprocesses WHERE blocked > 0) a
  WHERE NOT EXISTS (SELECT * FROM (SELECT * FROM sysprocesses WHERE blocked > 0) b WHERE a.blocked = b.spid)
  UNION SELECT spid, blocked FROM sysprocesses WHERE blocked > 0;

  SELECT @cnt = COUNT(*) FROM #tmp;
  IF @cnt = 0 BEGIN PRINT '目前無阻塞'; RETURN; END

  WHILE @i <= @cnt
  BEGIN
    SELECT @spid = spid, @bl = bl FROM #tmp WHERE id = @i;
    IF @spid = 0
      PRINT '阻塞源頭: SPID ' + CAST(@bl AS VARCHAR) + '，其執行 SQL:';
    ELSE
      PRINT 'SPID ' + CAST(@spid AS VARCHAR) + ' 被 SPID ' + CAST(@bl AS VARCHAR) + ' 阻塞，當前 SQL:';
    DBCC INPUTBUFFER (@bl);
    SET @i = @i + 1;
  END
  DROP TABLE #tmp;
  RETURN 0;
END; GO

二、sp_Kill_lockProcess：一鍵解除阻塞

CREATE PROC sp_Kill_lockProcess AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @spid INT, @bl INT, @cnt INT, @i INT = 1, @sql NVARCHAR(200);
  CREATE TABLE #tmp (id INT IDENTITY(1,1), spid SMALLINT, bl SMALLINT);

  INSERT #tmp (spid, bl)
  SELECT 0, blocked FROM (SELECT * FROM sysprocesses WHERE blocked > 0) a
  WHERE NOT EXISTS (SELECT * FROM (SELECT * FROM sysprocesses WHERE blocked > 0) b WHERE a.blocked = b.spid)
  UNION SELECT spid, blocked FROM sysprocesses WHERE blocked > 0;

  SELECT @cnt = COUNT(*) FROM #tmp;
  WHILE @i <= @cnt
  BEGIN
    SELECT @spid = spid, @bl = bl FROM #tmp WHERE id = @i;
    IF @spid = 0
    BEGIN
      SET @sql = 'KILL ' + CAST(@bl AS VARCHAR);
      EXEC sp_executesql @sql;
      PRINT '已 KILL 阻塞源頭 SPID ' + CAST(@bl AS VARCHAR);
    END
    SET @i = @i + 1;
  END
  DROP TABLE #tmp;
  SET NOCOUNT OFF;
  RETURN 0;
END; GO

三、進階：查詢 syslockinfo 詳細鎖資源

CREATE TABLE #t (req_spid INT, obj_name SYSNAME);
DECLARE @s NVARCHAR(4000), @rid INT, @dbname SYSNAME, @id INT, @objname SYSNAME;
DECLARE tb CURSOR FOR
  SELECT DISTINCT req_spid, DB_NAME(rsc_dbid), rsc_objid
  FROM master..syslockinfo WHERE rsc_type IN (4,5); -- 4=Index, 5=Table
OPEN tb;
FETCH NEXT FROM tb INTO @rid, @dbname, @id;
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @s = 'SELECT @objname=name FROM [' + @dbname + ']..sysobjects WHERE id=@id';
  EXEC sp_executesql @s, N'@objname SYSNAME OUT, @id INT', @objname OUT, @id;
  INSERT #t VALUES (@rid, @objname);
  FETCH NEXT FROM tb INTO @rid, @dbname, @id;
END
CLOSE tb; DEALLOCATE tb;

SELECT 進程id = a.req_spid,
       資料庫 = DB_NAME(rsc_dbid),
       類型 = CASE rsc_type
         WHEN 1 THEN 'NULL' WHEN 2 THEN 'Database' WHEN 3 THEN 'File'
         WHEN 4 THEN 'Index' WHEN 5 THEN 'Table' WHEN 6 THEN 'Page'
         WHEN 7 THEN 'Key' WHEN 8 THEN 'Extent' WHEN 9 THEN 'RID'
         WHEN 10 THEN 'Application' END,
       對象id = rsc_objid, 對象名 = b.obj_name, rsc_indid
FROM master..syslockinfo a LEFT JOIN #t b ON a.req_spid = b.req_spid;
DROP TABLE #t;