[SQL] 透過擴充事件來捕捉 DeadLock

擴充事件預設啟用的 System_Health ，就包含 DeadLock 相關資訊

官方文章內容 - System_Health 所收集的資訊

• The sql_text and session_id for any sessions that encounter an error that has a severity >= 20.
• The sql_text and session_id for any sessions that encounter a memory-related error. The errors include 17803, 701, 802, 8645, 8651, 8657 and 8902.
• A record of any non-yielding scheduler problems. These appear in the SQL Server error log as error 17883.
• Any deadlocks that are detected, including the deadlock graph.
• The callstack, sql_text, and session_id for any sessions that have waited on latches (or other interesting resources) for > 15 seconds
• The callstack, sql_text, and session_id for any sessions that have waited on locks for > 30 seconds.
• The callstack, sql_text, and session_id for any sessions that have waited for a long time for preemptive waits. The duration varies by wait type. A preemptive wait is where SQL Server is waiting for external API calls.
• The callstack and session_id for CLR allocation and virtual allocation failures.
• The ring buffer events for the memory broker, scheduler monitor, memory node OOM, security, and connectivity.
• System component results from sp_server_diagnostics.
• Instance health collected by scheduler_monitor_system_health_ring_buffer_recorded.
• CLR Allocation failures.
• Connectivity errors using connectivity_ring_buffer_recorded.
• Security errors using security_error_ring_buffer_recorded.

透過之前筆記 - [SQL] 模擬死結產生 來產生死結，並在擴充事件內觀察 DeadLock，以下為 SSMS 操作步驟

利用 sys.fn_xe_file_target_read_file 來抓取 DeadLock 相關資訊

SELECT 
	-- DeadLock XML 內容
	CONVERT(xml,event_data).query('/event/data/value/deadlock') as DeadLockGraph ,
    
	-- 直接抓 DeadLock XML 內的 timestamp
	CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime')  AS Execution_Time ,
    
	-- timestamp_utc 欄位資訊在 SQL Server 2017 才開始提供
	timestamp_utc
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE [object_name] = 'xml_deadlock_report'
ORDER BY timestamp_utc DESC

sys.fn_xe_file_target_read_file 抓出來的 DeadLock XML 檔案，另存為 xdl 後，再利用 SSMS 開啟，就可以看見 DeadLock Graph

• 參考資料
• 使用 system_health 工作階段
• sys.fn_xe_file_target_read_file
• [SQL Server][DeakLock]觀察死結的工具(四)擴充事件(Extended events)
• Extracting Deadlock information using SYSTEM_HEALTH Extended Events