SQL Server 2008 擴充事件 入門：以 Extended Events 取代 SQL Trace 進行阻塞診斷

為什麼需要 Extended Events (XEvent)？

• 取代舊有 SQL Trace / Profiler，架構更輕量
• 事件收集成本可預測、極低，適合生產環境長期開啟
• 可同步捕捉 執行時上下文：呼叫堆疊、執行計畫控制碼、記憶體狀態等
• 高度可設定：依負載動態啟用/停用特定事件

實戰案例：找出封鎖來源 Query

發現 sys.dm_exec_requests 顯示 Session 54 SUSPENDED 等待鎖，但看不出誰持有鎖、執行計畫、T-SQL 堆疊。預先建立 XEvent Session 即可完整記錄。

步驟 1：建立 XEvent Session (捕捉鎖取得/釋放 + 上下文)

-- 清理同名 Session
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'FindBlockers')
    DROP EVENT SESSION FindBlockers ON SERVER
GO

DECLARE @dbid INT = DB_ID('DAA')  -- 目標資料庫
IF @dbid IS NULL
BEGIN
    RAISERROR('DAA not found', 17, 1)
    RETURN
END

DECLARE @sql NVARCHAR(MAX) = N'
CREATE EVENT SESSION FindBlockers ON SERVER
ADD EVENT sqlserver.lock_acquired
(
    ACTION (sqlserver.sql_text, sqlserver.database_id, sqlserver.tsql_stack,
            sqlserver.plan_handle, sqlserver.session_id)
    WHERE (database_id = ' + CAST(@dbid AS NVARCHAR) + ' AND resource_0 != 0)
)
ADD EVENT sqlserver.lock_released
(
    WHERE (database_id = ' + CAST(@dbid AS NVARCHAR) + ' AND resource_0 != 0)
)
ADD TARGET package0.pair_matching
(
    SET begin_event = ''sqlserver.lock_acquired'',
        begin_matching_columns = ''database_id, resource_0, resource_1, resource_2, transaction_id, mode'',
        end_event = ''sqlserver.lock_released'',
        end_matching_columns = ''database_id, resource_0, resource_1, resource_2, transaction_id, mode'',
        respond_to_memory_pressure = 1
)
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)'

EXEC (@sql)
GO

-- 啟動 Session
ALTER EVENT SESSION FindBlockers ON SERVER STATE = START
GO

步驟 2：查詢未配對的鎖取得事件 (即持有鎖未釋放的 Blocker)

-- 從 pair_matching 目標取得未配對事件 XML
SELECT
    objlocks.value('(action/value)[5]', 'int') AS session_id,
    objlocks.value('(data/value)[5]', 'int') AS database_id,
    objlocks.value('(data/text)[1]', 'nvarchar(50)') AS resource_type,
    objlocks.value('(data/value)[9]', 'bigint') AS resource_0,
    objlocks.value('(data/value)[10]', 'bigint') AS resource_1,
    objlocks.value('(data/value)[11]', 'bigint') AS resource_2,
    objlocks.value('(data/text)[2]', 'nvarchar(50)') AS mode,
    objlocks.value('(action/value)[1]', 'varchar(max)') AS sql_text,
    CAST(objlocks.value('(action/value)[4]', 'varchar(max)') AS XML) AS plan_handle,
    CAST(objlocks.value('(action/value)[3]', 'varchar(max)') AS XML) AS tsql_stack
INTO #unmatched_locks
FROM (
    SELECT CAST(xest.target_data AS XML) AS lockinfo
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xest.target_name = 'pair_matching' AND xes.name = 'FindBlockers'
) heldlocks
CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks)

-- 關聯 dm_tran_locks 與 dm_exec_requests 找出真正阻塞他人的 Session
SELECT ul.*
FROM #unmatched_locks ul
INNER JOIN sys.dm_tran_locks tl
    ON ul.database_id = tl.resource_database_id
   AND ul.resource_type = tl.resource_type
WHERE ul.resource_0 IS NOT NULL
  AND ul.session_id IN (
      SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0
  )
  AND tl.request_status = 'wait'

結果解讀

回傳資料列包含 session_id (持有鎖的 Blocker)、sql_text (完整 T-SQL)、tsql_stack (呼叫堆疊)、plan_handle (可輸入 sys.dm_exec_query_plan 看圖形化計畫)。

範例中發現 Session 53 執行 BEGIN TRAN UPDATE BlockTest SET Col1='Updated' WHERE ID=1 但缺少 COMMIT，導致 Session 54 後續查詢被封鎖。

收尾

ALTER EVENT SESSION FindBlockers ON SERVER STATE = STOP
DROP EVENT SESSION FindBlockers ON SERVER