為什麼需要擴充事件？
- SQL Trace / Profiler 效能負擔高、篩選不靈活、輸出後處理麻煩。
- DMV 無法捕捉某些底層事件 (如鎖升級、頁面分割、查詢計畫 XML)。
- 擴充事件 (Extended Events, XEvents) 是下一代事件收集架構：同步觸發、非同步消費、豐富謂詞篩選、任意動作綁定、多目標輸出 (環狀緩衝、檔案、ETW、計數器)。
核心概念
| 術語 | 說明 |
|---|
| Event | 程式碼中定義的檢測點 (如 sql_statement_completed、lock_acquired)，含預定義負載欄位。 |
| Predicate | 布林邏輯篩選器，同步評估，決定事件是否被消費 (如 duration > 1000000、database_id = 5)。 |
| Action | 事件消費前同步執行的附加操作 (如 sql_text、query_plan、session_id)，僅在謂詞通過後執行。 |
| Target | 事件消費者：ring_buffer (記憶體環狀)、event_file (磁碟檔案)、event_counter、histogram、pair_matching 等。 |
| Session | 事件、動作、謂詞、目標的組合單位，可啟動/停止/修改。 |
常用 DMV 探索中繼資料
-- 所有可用事件
SELECT xp.name AS package, xo.name AS event, xo.description
FROM sys.dm_xe_objects xo
JOIN sys.dm_xe_packages xp ON xp.guid = xo.package_guid
WHERE xo.object_type = 'event'
ORDER BY xp.name, xo.name;
-- 特定事件的負載欄位
SELECT * FROM sys.dm_xe_object_columns WHERE object_name = 'sql_statement_completed';
-- 所有可用動作
SELECT xp.name, xo.name, xo.description
FROM sys.dm_xe_objects xo
JOIN sys.dm_xe_packages xp ON xp.guid = xo.package_guid
WHERE xo.object_type = 'action'
ORDER BY xp.name, xo.name;
-- 所有目標
SELECT xp.name, xo.name, xo.description
FROM sys.dm_xe_objects xo
JOIN sys.dm_xe_packages xp ON xp.guid = xo.package_guid
WHERE xo.object_type = 'target'
ORDER BY xp.name, xo.name;
實戰案例：監控資源池 I/O 用量 (Resource Governor 計費)
建立 Session
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'MonitorIO')
DROP EVENT SESSION MonitorIO ON SERVER;
CREATE EVENT SESSION MonitorIO ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.session_resource_pool_id) -- 取得資源池 ID
)
ADD TARGET package0.ring_buffer -- 記憶體環狀緩衝，適合原型開發
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS);
啟動 / 停止
ALTER EVENT SESSION MonitorIO ON SERVER STATE = START;
-- ... 執行負載測試 ...
ALTER EVENT SESSION MonitorIO ON SERVER STATE = STOP;
查詢結果 (XML 解析)
WITH RawData AS (
SELECT CAST(xest.target_data AS XML) AS target_xml
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
WHERE xes.name = 'MonitorIO' AND xest.target_name = 'ring_buffer'
),
Events AS (
SELECT
n.value('(data[@name="reads"]/value)[1]', 'BIGINT') AS reads,
n.value('(data[@name="writes"]/value)[1]', 'BIGINT') AS writes,
n.value('(action[@name="session_resource_pool_id"]/value)[1]', 'INT') AS pool_id
FROM RawData
CROSS APPLY target_xml.nodes('//RingBufferTarget/event') AS t(n)
)
SELECT pool_id, SUM(reads) AS total_reads, SUM(writes) AS total_writes
FROM Events
WHERE pool_id > 255 -- 系統池 ID ≤ 255
GROUP BY pool_id;
效能調校關鍵參數
| 參數 | 建議 |
|---|
MAX_MEMORY | 預設 4MB，高頻事件建議 64-256MB。 |
EVENT_RETENTION_MODE | ALLOW_SINGLE_EVENT_LOSS (預設) 平衡效能；NO_EVENT_LOSS 會阻塞產生事件的執行緒，謹慎使用。 |
MAX_DISPATCH_LATENCY | 預設 30 秒，即時分析需求可降至 5-10 秒。 |
MEMORY_PARTITION_MODE | NUMA 環境建議 PER_CPU 或 PER_NODE 減少記憶體爭用。 |
內建 system_health Session
- 安裝後預設啟動，收集死結、嚴重錯誤、等待統計、排程器健康等關鍵資訊。
- 目標為
ring_buffer + event_file，不佔用大量資源。
- 查看：
SELECT CAST(target_data AS XML) FROM sys.dm_xe_session_targets WHERE target_name = 'ring_buffer' AND event_session_address = (SELECT address FROM sys.dm_xe_sessions WHERE name = 'system_health');
遷移自 Profiler 的最佳實務
- 改用
event_file 目標持久化儲存，配合 sys.fn_xe_file_target_read_file 讀取。
- 善用 謂詞 在來源端過濾，避免大量事件傳輸至目標再篩選。
- 避免在高頻事件 (如
sql_statement_completed) 綁定昂貴動作 (query_plan)；改用 sql_text + 事後關聯計畫控制代碼。
- 定期清理舊 Session 與事件檔案，避免佔滿磁碟。
結語
擴充事件雖需學習 XML 解析，但其極低負擔、極高彈性、深度可見性，使其成為現代 SQL Server 故障診斷的核心工具。建議 DBA 將 Profiler 退休，全面轉向 XEvents。
