數據庫阻塞，5分鐘後殺死負責的會話

過去幾個月以來，我一直在努力解決我的數據庫伺服器上的數據庫阻塞問題。已經嘗試使用多種方法來擷取他們，並且還知道誰是封鎖的領導者。此應用程序使用典型的主鍵-外鍵關係，並且所有表都是緊密耦合的。很少有表有多達 30 個外鍵，因此每當有插入/更新/刪除時，所有這些基礎表都會被阻塞。我已將此資訊傳達給供應商，他們還嘗試為可能的表刪除 PK-FK 並部署在測試環境中，但是由於需要測試很多內容並且在某些或其他情況下失敗，因此它需要很長時間才能投入生產。

因為，我支持基於供應商的應用程序，並且任何更改都需要由多個利益相關者進行測試，我不知道它什麼時候才能真正投入生產。阻塞變得如此嚴重，以至於整個門戶都關閉了，我別無選擇，只能殺死阻塞的 SPID。

我正在使用以下查詢通過使用 sp_whoisactive 過程 Mr. Adam Machanic 檢查阻塞：

EXEC sp_WhoIsActive
   @find_block_leaders = 1,
   @sort_order = '[blocked_session_count] DESC',

到目前為止，我一直在研究反應性方法，我正在殺死負責阻塞的 SPID，但這似乎並不順利，有時阻塞升級，我必須像消防員一樣奔跑。我想將我的立場從被動轉變為主動，併計劃實施一個程序，該程序將從上述程序作為 SPID 輸入，檢查持續時間，如果阻塞超過 5 分鐘，則將其終止。

有時，我看到有許多攔截器負責攔截，如下所示：

真的不知道如何處理這種阻塞。在此連結上閱讀 Jason Hall 先生的類似內容，在此論壇上閱讀第二個內容，但是我正在尋找具有 sp_whoisactive 基礎的內容。

這是為了添加關於數據庫隔離的非常相關的一點，我已將數據庫隔離級別從預設更改為“讀取送出的快照”。在同一個主題上，我在加入這個論壇時提出了這個問題，但是由於我對這個論壇很陌生並且對我的問題不清楚，所以它被關閉了。

對此的任何輸入/幫助將不勝感激。

版本：Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 版權所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows NT 6.3 (Build 14393:) (管理程序）

您可以使用我在下面編寫的 SQL 腳本建構一個動態 SQL 字元串，該字元串將終止所有已執行至少 5 分鐘的阻塞會話。

您需要創建一個執行此 SQL 腳本的 SQL 代理作業 ( SQL Agent Job Docs )。（如果需要，您可以將 SQL 代理作業安排為每秒執行一次，但代理作業 GUI 僅允許您在安排時選擇至少 1 分鐘，因此如果您需要更高的頻率，您必須編寫作業腳本.)

-- Variable to hold dynamic SQL of what blocking sessions will be killed
DECLARE @DynamicSQL AS NVARCHAR(MAX) = '';

-- CTE of blocking sessions
WITH CTE_BlockingSessions AS
(
   SELECT blocking_session_id
   FROM sys.dm_exec_requests
   WHERE blocking_session_id <> 0
)

-- Sets the @DynamicSQL variable = 'KILL ' + all blocking session IDs that have been running for at least 5 minutes
SELECT @DynamicSQL = @DynamicSQL + 'KILL ' + CAST(BS.blocking_session_id AS VARCHAR(10)) + ';'
FROM sys.dm_exec_requests AS ER 
INNER JOIN CTE_BlockingSessions AS BS
   ON ER.session_id = BS.blocking_session_id
WHERE DATEDIFF(s, ER.start_time, GETDATE()) >= 300 -- 300 seconds = 5 minutes
   AND ER.[status] = 'RUNNING' -- Ensures the blocking session is still active

-- PRINT @DynamicSQL -- For testing, outputs the dynamic SQL to SSMS Messages tab
EXEC sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQL

—獲取執行時間超過 5 分鐘的程序列表

SELECT 

   spid,status,loginame,hostname,DB_NAME(dbid),cmd,cpu,physical_io,DATEDIFF(MI,LastBatch,GETDATE()) EventTime ,program_name 
           FROM SYS.SYSPROCESSES
           WHERE 
               1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1
               --Transactions that are open not yet committed or rolledback
                           WHEN Status = 'SLEEPING' AND open_tran  > 0 THEN 1 
                           ELSE 0 END
               AND cmd NOT LIKE 'BACKUP%'
               AND DATEDIFF(MI,LastBatch,GETDATE())>5 -- greater than 5 mins

—獲取上述查詢返回的每個 SPID 的詳細資訊

DBCC INPUTBUFFER( @SPID ) 

—殺死程序，如果它必須是

KILL @SPID

引用自：https://dba.stackexchange.com/questions/234175