[SQL] Blocked Process Threshold - Extended Events

已封鎖的處理序臨界值伺服器組態選項 文章內容

• 使用 blocked process threshold 選項，以秒為單位來指定產生已封鎖處理序報表的臨界值。 預設不會針對已封鎖的處理序產生任何報告。 對於系統工作或在等待不產生可偵測死結的資源的工作，並不會產生此事件。

• 不保證即時或甚至接近即時的報告。

設定伺服器組態 - 「已封鎖的處理序臨界值 (blocked process threshold)」

設定重點

• 根據需求設定鎖定秒數需觸發 blocking 事件，該篇筆記是設定 15 秒
• 設定值為 0 到 86,400，但設定秒數必須大於等於 5，設定 1 - 4 秒，會造成死結監視器不斷執行，不建議長期使用，詳見 增加或停用 Blocked Process Threshold
• 設定立即生效，伺服器不必停止再重新啟動

利用語法設定

EXECUTE sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXECUTE sp_configure 'blocked process threshold', 15 -- 設定 15 秒
GO
RECONFIGURE
GO
EXECUTE sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

利用 SSMS 設定

設定 Extended Events

一般：新增工作階段 - [Block Process Threadhold Demo]，並勾選 [在伺服器啟動時啟動事件工作階段] 和 [在工作階段建立後立即啟動工作階段]

事件：透過搜尋功能，搜尋 Blocked 關鍵字，並選取 [blocked_process_report] 事件

資料存放區：加入 [event_file] 類型，並指定檔案存放位置

設定完後可以利用 [指令碼] 功能，產生 Extended Events Script 來觀察

CREATE EVENT SESSION [Blocked Process Threshold Demo] ON SERVER 
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file(SET filename=N'D:\Blocked Process Threshold Demo.xel')
WITH (STARTUP_STATE=ON)
GO

利用 TSQL 產生 block 並開啟 [監看即時資訊] 來觀察或 sys.fn_xe_file_target_read_file 查詢 block 資訊

--在 TempDB 建立 tblBlockDemo 資料表，並透過 Begin Transaction 開啟交易，並鎖定資料表 20 秒
USE [TempDB]
GO

CREATE TABLE tblBlockDemo (ID int identity primary key)
GO

BEGIN TRANSACTION
INSERT INTO tblBlockDemo DEFAULT VALUES
WAITFOR DELAY '00:00:20'
COMMIT

--開啟另一個 T-SQL 視窗執行以下語法，查詢資料表 tblBlockDemo
USE [TempDB]
GO
SELECT * FROM tblBlockDemo

從即時訊息中就可以看到 block 資訊

點選 blocked_process 就可以叫出 xml 來觀察 block 細節

利用 sys.fn_xe_file_target_read_file 查詢

SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.query('(event/data[@name="blocked_process"]/value/blocked-process-report)[1]') as [blocked_process_report]
FROM
(
    SELECT 
        CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('D:\Blocked Process Threshold Demo*.xel', NULL, NULL, NULL)
) AS T
GO

• 參考資料
• 已封鎖的處理序臨界值伺服器組態選項
• 增加或停用 Blocked Process Threshold
• [SQL SERVER]找出封鎖的處理序
• [SQL Server][Lock]透過擴充事件找鎖定的物件以及被誰Blocking
• Finding Blocked Processes and Deadlocks using SQL Server Extended Events