2014-09-05

如何追查 SQL Server 的 Deadlock

最近有個客戶常遇到 SQL Server 的 Deadlock 問題,查了一下,追踨 Deadlock 有兩個做法(實際應該是一樣的):
  1. 使用 SQL Server Profiler 去記錄
  2. 直接下 SQL 新增追踨

以下是使用 SQL 指令追踨的方法:

  1. 建立追踨 Deadlock (假定追踨檔是要存在 D:\DbLog\ 下)
     -- 運行下述語句後,每當SQL Server中發生死鎖事件,都會自動往文件e:\DbLog\deadlockdetect.trc 中插入一條記錄。
     -- 定義參數 
     declare @rc int
     declare @TraceID int
     declare @maxfilesize bigint
     set @maxfilesize = 5
     -- 初始化跟蹤 
     exec @rc = sp_trace_create @TraceID output, 0, N'E:\DbLog\deadlockdetect', @maxfilesize, NULL
     --    此處的e:\dblog\deadlockdetect是文件名(可自行修改),SQL會自動在後面加上.trc的擴展名 
     if (@rc != 0) goto error 
     -- 設置跟蹤事件 
     declare @on bit
     set @on = 1
     --下述語句中的148指的是locks:deadlock graph事件(參見sys.trace_events),12指的是spid列(參見    sys.trace_columns) 
     exec sp_trace_setevent @TraceID, 148, 1, @on
     exec sp_trace_setevent @TraceID, 148, 4, @on
     exec sp_trace_setevent @TraceID, 148, 11, @on
     exec sp_trace_setevent @TraceID, 148, 12, @on
     exec sp_trace_setevent @TraceID, 148, 14, @on
     exec sp_trace_setevent @TraceID, 148, 26, @on
     exec sp_trace_setevent @TraceID, 148, 41, @on
     exec sp_trace_setevent @TraceID, 148, 51, @on
     exec sp_trace_setevent @TraceID, 148, 60, @on
     exec sp_trace_setevent @TraceID, 148, 64, @on
     -- 啟動跟蹤 
     exec sp_trace_setstatus @TraceID, 1
     -- 記錄下跟蹤ID,以備後面使用 
     select TraceID = @TraceID
     goto finish 
     error: 
     select ErrorCode=@rc
     finish: 
     go
    
  2. 當發生 Deadlock 後,去查看 deadlockdetect.trc 是哪些 SQL 有問題 (結果中的TextData列即以XML的形式返回死鎖的詳細信息。 )
     select fn_trace_gettable('e:\DbLog\deadlockdetect.trc', 1)
    
  3. 查詢、暫停和停止服務器端跟蹤
    查詢目前有哪些 Traces 正在執行
     SELECT * FROM sys.traces
    
    暫停某個 Trace
     -- 第一個參數表示TraceID,即步驟1中的輸出參數(或是從 sys.traces 查詢得到)。第二個參數表示將狀態改為0,即暫停
     exec sp_trace_setstatus 2, 0
    
    中止某個 Trace
     -- 第一個參數表示TraceID,即步驟1中的輸出參數(或是從 sys.traces 查詢得到)。第二個參數表示將狀態改為2,即停止
     exec sp_trace_setstatus 2, 2
    
  4. 在 Deadlock 的情況下,也可以直接查詢目前正在執行的 SQL
    SELECT sqltext.TEXT,req.session_id,
     req.status,
     req.command,
     req.cpu_time,
     req.total_elapsed_time,
     s.host_name,
     s.login_time,
     s.login_name
    FROM sys.dm_exec_requests req
     CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
     INNER JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id
    where DB_NAME(req.database_id) = '資料庫名稱'
    

沒有留言: