除了Trace flag及SQL Profiler能野生捕獲DeadLock資訊,在SQL Server 2008開始,也多了一個擴充事件(Extended events)的工具可以幫助我們,到了SQL Server 2016,雖然Trace flag及SQL Profiler都還能運作,但考慮到SQL Profiler對於Server負載的衝擊以及將來SQL產品的支援,我們就是慢慢把系統診斷的工作轉移到擴充事件中嚕~
建立及啟動擴充事件(Extended events)
(1)建立擴充事件(Event Session)
CREATE EVENT SESSION DeadLockEvent ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
(
SET FILENAME = N'C:\temp\db\DeadLockEvent.xet',
METADATAFILE = 'C:\temp\db\DeadLockEvent.xem'
)
(2)啟動擴充事件(Event Session)
ALTER EVENT SESSION DeadLockEvent
ON SERVER
--ON DATABASE
STATE = START; -- STOP;
執行測試死結的語法
只要讓兩個交易互斥鎖定物件時間長 (15秒)而且存取Table資源的順序不同。
查詢視窗1(先更新t1再更新t2)
BEGIN Tran Tran_one
UPDATE t1
set c2 = 'lock'
WAITFOR DELAY '00:00:15';
UPDATE t2
set c3 = 'lock'
ROLLBACK
查詢視窗2(先更新t2再更新t1):必須在15秒內執行
BEGIN Tran Tran_two
UPDATE t2
set c3 = 'lock'
WAITFOR DELAY '00:00:15';
UPDATE t1
set c2 = 'lock'
ROLLBACK
查詢視窗1 x 2的執行結果:
查詢視窗2出現了以下的錯誤訊息:
查詢擴充事件結果有兩個方式,一個是直接透過SSMS GUI在物件總管下的管理直接查詢,另一個則是查詢.xet event 檔案,有時候也會權限管控,也許就可以透過這個方式取出Deadlock xml內容。
查詢擴充事件結果:透過SSMS GUI
SSMS物件總管 > 管理 > 擴充事件 > 點開DeadLockEvent > 點擊兩下event file
點開錄製到的死結,噹噹! Deadlock Graph本人
查詢擴充事件結果:透過T-SQL
查詢Event結果,將event data轉換成xml type再下去用xml.query查Deadlock Graph所需的結構及資料(.xdl)
SELECT object_name
,file_name
,convert(xml,event_data).query('/event/data/value/deadlock') as DeadLockGraph
FROM sys.fn_xe_file_target_read_file('C:\temp\db\*.xet', null , null, null);
DECLARE @xml xml
SELECT @xml = event_data
FROM sys.fn_xe_file_target_read_file('C:\temp\db\*.xet', null , null, null);
SELECT @xml.query('/event/data/value/deadlock')
查詢結果,點擊xml超連結
將deadlockGraph xml結果儲存成.xdl檔案
Ctrl + S 儲存成.xdl檔案
重新開啟.xdl檔案
熱鍵 Alt + F + F
噹噹!
再次見到Deadlock Graph本人
小結:
- 嗯~擴充事件相較Trace flag及SQL Profiler有方便到,就看大家順手和習慣嚕。
- 擴充事件也可以使用精靈GUI產生,推薦大家參考RiCo大的文章。
RiCo技術農場 [SQL SERVER][Maintain]監控Deadlock
https://dotblogs.com.tw/ricochen/archive/2013/10/20/124827.aspx
參考:
Quick Start: Extended events in SQL Server
https://msdn.microsoft.com/en-us/library/mt733217.aspx
sys.fn_xe_file_target_read_file (Transact-SQL)
https://msdn.microsoft.com/en-us/library/cc280743.aspx
CREATE EVENT SESSION (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/bb677289.aspx
sql xml query() 方法 (xml 資料類型)