[SQL SERVER]找出封鎖的處理序
今年4月中系統上線第一天因為suspended相當高，
這篇介紹透過 blocked process threshold 組態來找出blocked process兇手。
1.啟用並設定10秒門檻值
sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 10;
GO
RECONFIGURE ;
GO
只要封鎖超過10秒就會觸發 Blocked Process Report Event Class，但需注意該值建議要>=5，
因為1~4會造成死結監控處理序不斷執行，而且該值也不建議長久啟用。
2.啟用server trace
可參考[SQL SERVER][Maintain]匯出指令碼追蹤定義產生如下script
選擇Blocked process report event。
修改檔案路徑和停止時間並啟動trace。
3.模擬blocked process
session1
select * from DDTest
session2
begin tran
update DDTest set col1 = 100 + 1
waitfor delay '00:00:15'
rollback
4.停止trace
--停止trace
exec sp_trace_setstatus 2, 0
--刪除trace但保留檔案
exec sp_trace_setstatus 2, 2
5.View blocked process report(記得停用trace exec sp_trace_setstatus 2, 0)
可以參考[SQL SERVER][Maintain]如何把追蹤檔(trc)匯入資料表
我這裡直接查詢trace file
select SPID,cast(TextData as xml),EndTime, Duration
from fn_trace_gettable(N'e:\findblocked.trc', default)
where eventclass = 137
我們來看一下輸出的xml檔案是否有如實抓到
可以明確看到相關SQL Statement都有詳細資訊，spid52 等待資源OBJECT: 7:1892201791:0。
最後停用 sp_configure 'blocked process threshold' ,0
參考
blocked process threshold Server Configuration Option
Increase or Disable Blocked Process Threshold
Blocked Process Report Event Class
SQL SERVER – Find Blocking Using Blocked Process Threshold