學海無邊，書囊無底

使用SQL 2012提供擴充事件追蹤Blocking事件

在事件庫輸入 block 字串，搜尋有關封鎖事件的內容，如下圖所示。透過左右按鈕可以指定要追蹤的事件。

選好blocked_process_report 事件後，經由上方的 [設定 ]按鈕，可切換到進階的設定內容。如下圖所示，共有三個頁籤可設定。在[全域欄位]可觀察到它提供了SQL 資料庫引擎之外的其他相關追蹤，例如CPU、記憶體…。[篩選]頁籤可以設定事件要記錄的項目。

[事件欄位]則列出此事件所提供的所有可用清單，以及欄位屬性。

完成設定後即可點選 [確定] 按鈕。若想進一步了解相關的T-SQL指令，可點選上方的[指令碼]按鈕。指令碼產生的結果如下圖所示。

CREATE EVENT SESSION MonitorBlocking
ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.ring_buffer(SET MAX_MEMORY=2048)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO
ALTER EVENT SESSION MonitorBlocking
ON SERVER
STATE=START
GO

最後會在擴充事件底下看到建立後的結果。

接著可以透過以下步驟，測試擴充事件追蹤的效果。首先，使用SSMS工具連結SQL Server並執行以下語法。使用Begin Transation指令開啟交易，於t1資料表新增一筆資料。

USE [tempdb]
GO
CREATE TABLE t1 (RowID int identity primary key)
GO
BEGIN TRANSACTION
INSERT INTO t1 DEFAULT VALUES
WAITFOR DELAY '00:00:30'
COMMIT

USE [tempdb]
GO
SELECT * FROM 

SELECT CAST(target_data AS XML) as target_data
        FROM sys.dm_xe_sessions AS s    
        JOIN sys.dm_xe_session_targets AS t
            ON s.address = t.event_session_address
        WHERE s.name = 'MonitorBlocking'
          AND t.target_name = 'ring_buffer'

檢視系統資料表

利用T-SQL查詢語法取得XML相關資訊

-- Query the XML to get the Target Data
SELECT 
    n.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    n.value('(event/@package)[1]', 'varchar(50)') AS package_name,
    DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
    ISNULL(n.value('(event/data[@name="database_id"]/value)[1]', 'int'),
            n.value('(event/action[@name="database_id"]/value)[1]', 'int')) as [database_id],
    n.value('(event/data[@name="database_name"]/value)[1]', 'nvarchar(128)') as [database_name],
    n.value('(event/data[@name="object_id"]/value)[1]', 'int') as [object_id],
    n.value('(event/data[@name="index_id"]/value)[1]', 'int') as [index_id],
    CAST(n.value('(event/data[@name="duration"]/value)[1]', 'bigint')/1000000.0 AS decimal(6,2)) as [duration_seconds],
    n.value('(event/data[@name="lock_mode"]/text)[1]', 'nvarchar(10)') as [file_handle],
    n.value('(event/data[@name="transaction_id"]/value)[1]', 'bigint') as [transaction_id],
    n.value('(event/data[@name="resource_owner_type"]/text)[1]', 'nvarchar(10)') as [resource_owner_type],
    CAST(n.value('(event/data[@name="blocked_process"]/value)[1]', 'nvarchar(max)') as XML) as [blocked_process_report]
FROM
(    SELECT td.query('.') as n
    FROM 
    (
        SELECT CAST(target_data AS XML) as target_data
        FROM sys.dm_xe_sessions AS s    
        JOIN sys.dm_xe_session_targets AS t
            ON s.address = t.event_session_address
        WHERE s.name = 'MonitorBlocking'
          AND t.target_name = 'ring_buffer'
    ) AS sub
    CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
) as tab
GO

補充：

blocked process threshold 選項設定為 0 (已停用)，使用上述範例進行監控時，需啟用blocked process threshold 。請執行以下語法設定每15秒追蹤一次。切勿將 blocked process threshold 選項設定為 5以下，這會造成死結監視器不斷執行。 參考線上說明

EXECUTE sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXECUTE sp_configure 'blocked process threshold', 15
GO
RECONFIGURE
GO
EXECUTE sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

參考：http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/21/an-xevent-a-day-21-of-31-the-future-tracking-blocking-in-denali.aspx

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/04/an-xevent-a-day-4-31-querying-the-session-definition-and-active-session-dmv-s.aspx