[SQL Server][DeakLock]觀察死結的工具(三)SQL Profiler

Profiler是SQL Server 2005開始提供的工具,一直以來都是我們診斷案件錄製SQL內部過程的好幫手,她可以讓我們建立和管理追蹤交易過程,蒐集到資訊後也能直接進行分析並且重新執行追蹤結果,我們來試試看捕捉DeadLock事件。

適用範圍:SQL Server 2005-2016

警示:使用時會耗費許多資料庫伺服器系統資源,如果DB已經負載很重了,要小心使用或是換成下一篇的擴充事件來偵測。

 


啟動SQL Server Profiler工具

1.啟動SQL Server Profiler

SSMS管理工具 > 工具 > SQL Server Profiler

或是

Windows鍵 + R + PROFILER.EXE

 

2.開啟Profiler後,首先要新增追蹤CtrlN

3.輸入要追蹤的連線資料庫帳號、密碼

帳號必須具備Trace Alter的權限。

 

4.連線要追蹤的資料庫後,跳出追蹤屬性的視窗。

事件選取範圍 > 勾選右下角顯示所有事件  > 點選 Deadlock graph、Lock: Deadlock、Lock: Deadlock Chain

事件擷取設定 > 個別儲存死結XML事件

設定死結XML檔案路徑

按下執行後開始Trace(錄製)

 


執行測試死結的語法

只要讓兩個交易互斥鎖定物件時間長 (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) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。

 

錄到DeadLock死結了,見到Deadlock Graph本人!

另外以SSMS開啟剛剛設定的死結XML檔案

Ctrl + O 

橢圓表示交易,滑鼠游標移動到圖形內時,會顯示交易使用的T-SQL指令碼。

中間的方形表示鎖定或是預備要求的資源。

藍色的x表示被犧牲的交易。

 


小結:

  • 使用deadlock graph觀察deadlock比較輕鬆。
  • 使用Profiler的使用者必須有 ALTER TRACE 權限
  • 另外msdn也有提到有關profiler及trace即將可能的退役警示,不過SQL Server 2016的版本都還能使用,如果順手了話,可以選擇下一篇要介紹的擴充事件(Extended events)試試嚕。

 

SQL Server Profiler

https://msdn.microsoft.com/en-us/library/ms181091.aspx

 

sp_trace_create (Transact-SQL)

https://msdn.microsoft.com/zh-tw/library/ms190362(v=sql.110).aspx

 


參考:

啟動 SQL Server Profiler

https://msdn.microsoft.com/library/ms173799.aspx

SQL Server Profiler

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

sp_trace_create (Transact-SQL)

https://msdn.microsoft.com/zh-tw/library/ms190362(v=sql.110).aspx