[SQL Server][DeakLock]觀察死結的工具(四)擴充事件(Extended events)

除了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出現了以下的錯誤訊息:

訊息 1205,層級 13,狀態 45,行 15                                                                                                                                                                                                                                                       交易 (處理序識別碼 55) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。

 

查詢擴充事件結果有兩個方式,一個是直接透過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 資料類型)

https://msdn.microsoft.com/zh-tw/library/ms191474.aspx