問題引入

系列SQL Server死鎖系列文章之二，講的是如何手動部署Profiler來捕獲死鎖以及對死鎖發時場景重現，這篇文章是將這個手動部署的過程自動化話，實現一鍵部署，既快捷方便，又簡單適用。上一篇文章，參見：使用Profiler捕獲死鎖。

自動部署Profiler

廢話不多說，直接上程式碼，只需要初始化下面的Profiler停止時間@stop_time即可：

use master
GO

set nocount on

declare 
    @trace_folder nvarchar(256)
    ,@trace_file nvarchar(256) 
    ,@max_files_size bigint
    
    ,@stop_time datetime
    ,@file_count int

    ,@int_filter_cpu int
    ,@int_filter_duration bigint
    ,@int_filter_spid int
    ,@set_trace_status int
;

select 
    @trace_folder=N`C:Tempperfmon`
    
    ,@max_files_size = 500            --max file size for each trace file
    ,@file_count = 10                --max file count
    
    ,@stop_time = `04/21/2017 22:10`    --null: stop trace manully; specify time (stop at the specify time)
    ,@int_filter_cpu = NULL                -- >= @int_filter_cpu ms will be traced. or else, skipped.
                                        --NULL: ignore this filter

    ,@int_filter_duration = NULL        --execution duration filter: millisecond
                                        --NULL: ignore this filter

    ,@int_filter_spid = NULL            --integer: specify a spid to trace
                                        --NULL: ignore this filter                
                                        
    ,@set_trace_status = 1    --0: Stops the specified trace.; 
                            --1: Starts the specified trace.;
                            --2: Closes the specified trace and deletes its definition from the server.;
;

/*

select * from sys.traces

*/
--private variables
declare
    @trace_id int
    ,@do int
    ,@loop int
    ,@trace_event_id int
    ,@trace_column_id int
    ,@return_code tinyint
    ,@return_decription varchar(200)
    ,@field_separator char(1)

;    
select
    @field_separator = `,`            --trace columns list separator
;

IF right(ltrim(rtrim(@trace_folder)), 1 ) <> ``
BEGIN
    SELECT 
        @trace_folder = ltrim(rtrim(@trace_folder)) + N`` 
    ;
    exec sys.xp_create_subdir @trace_folder
END
;

select
    @trace_file = @trace_folder + REPLACE(@@SERVERNAME, N``, N``)
;

IF @int_filter_spid IS NOT NULL
BEGIN
    select
        @trace_file = @trace_file + cast(@int_filter_spid as varchar)
    ;
END

--select @trace_file

select top 1
    @trace_id = id
from sys.traces
where path like @trace_file + N`%`

if @trace_id is not null
begin
    
    -- Start Trace (status 1 = start)
    EXEC sys.sp_trace_setstatus @trace_id, @set_trace_status

    return
end

if OBJECT_ID(`tempdb..#trace_event`,`u`) is not null
    drop table #trace_event
create table #trace_event
(
    id int identity(1,1) not null primary key
    ,trace_event_id int not null
    ,trace_column_id int not null
    ,event_name sysname null
    ,trace_column_name sysname null
)

;with trace_event
as
(        --select * from sys.trace_events where name like `%lock%` order by trace_event_id

    select 
        is_trace = 1 , event_name = `Deadlock graph`
        ,trace_column_list = `TextData,SPID,LoginName,StartTime,`
),
trace_column
as(
    select 
        *
        ,trace_column_list_xml = 
                                CAST(
                                        `<V><![CDATA[` 
                                                    + REPLACE(
                                                        REPLACE(
                                                                REPLACE(
                                                                            trace_column_list,CHAR(10),`]]></V><V><![CDATA[`
                                                                        ),@field_separator,`]]></V><V><![CDATA[`
                                                                ),CHAR(13),`]]></V><V><![CDATA[`
                                                            ) 
                                        + `]]></V>`
                                    as xml
                                )
    from trace_event
    where is_trace = 1
)
,data
as(
    select 
        trace_column = T.C.value(`(./text())[1]`,`sysname`)
        ,event_name
    from trace_column AS a
        CROSS APPLY trace_column_list_xml.nodes(`./V`) AS T(C)
)
INSERT INTO #trace_event
select 
    trace_event_id = ev.trace_event_id
    ,trace_column_id = col.trace_column_id
    ,a.event_name
    ,trace_column_name = a.trace_column
from data as a
    inner join sys.trace_columns as col
    on a.trace_column = col.name
    inner join sys.trace_events as ev
    on a.event_name = ev.name
where col.trace_column_id is not null
order by ev.trace_event_id
;

--select * from #trace_event

---private variables
select 
    @trace_id = 0
    ,@do = 1
    ,@loop = @@ROWCOUNT
    ,@trace_event_id = 0
    ,@trace_column_id = 0
    ,@return_code = 0
    ,@return_decription = ``
;

--create trace
exec @return_code = sys.sp_trace_create @traceid = @trace_id OUTPUT 
                                        , @options = 2  
                                        , @tracefile =  @trace_file
                                        , @maxfilesize = @max_files_size
                                        , @stoptime = @stop_time
                                        , @filecount =  @file_count
;

select 
    trace_id = @trace_id
    ,[current_time] = getdate()
    ,[stop_time] = @stop_time
;

set
    @return_decription = case @return_code
                                when 0 then `No error.`
                                when 1 then `Unknown error.`
                                when 10 then `Invalid options. Returned when options specified are incompatible.`
                                when 12 then `File not created.`
                                when 13 then `Out of memory. Returned when there is not enough memory to perform the specified action.`
                                when 14 then `Invalid stop time. Returned when the stop time specified has already happened.`
                                when 15 then `Invalid parameters. Returned when the user supplied incompatible parameters.`
                            else ``
                            end
;

raiserror(`Trace create with:
%s`,10,1,@return_decription) with nowait

--loop set trace event & event column
while @do <= @loop
begin
    select top 1
        @trace_event_id = trace_event_id
        ,@trace_column_id = trace_column_id
    from #trace_event
    where id = @do
    ;
    
    --set trace event
    exec sys.sp_trace_setevent @trace_id, @trace_event_id, @trace_column_id, 1
    raiserror(`exec sys.sp_trace_setevent @trace_id, %d, %d, 1`,10,1,@trace_event_id,@trace_column_id) with nowait
    
    set @do = @do + 1;
end

-- Set any filter. 
--CPU >= 500/ cpu columnid = 18
IF @int_filter_cpu IS NOT NULL
    EXEC sys.sp_trace_setfilter @trace_id, 18, 0, 4, @int_filter_cpu

--duration filter/ duration columnid=13
IF @int_filter_duration IS NOT NULL
    EXEC sys.sp_trace_setfilter @trace_id, 13, 0, 4, @int_filter_duration

--spid filter/ spid columnid=12
IF @int_filter_spid IS NOT NULL
    exec sys.sp_trace_setfilter @trace_id, 12, 0, 0, @int_filter_spid


--applicationName not like `SQL Server Profiler%`
EXEC sys.sp_trace_setfilter @trace_id, 10, 0, 7, N`SQL Server Profiler%`

-- Start Trace (status 1 = start)
EXEC sys.sp_trace_setstatus @trace_id, @set_trace_status
GO

--EXEC sys.sp_trace_setstatus 2, 0
--GO

select * from sys.traces

死鎖測試的方法和過程參見文章RDS SQL Server死鎖（Deadlock）系列之一使用DBCC捕獲死鎖中死鎖測試部分，在此不再累述。測試完畢後，死鎖資訊會一鍵部署完畢的Profiler程式監控抓取到，並存放在@trace_folder目錄下的一個以.trc結尾的檔案中，比如：C:TempperfmonCHERISH-PC.trc。

分析trc檔案

接下來的工作就是分析這個trc檔案，直觀Deadlock Graph圖分析方法和前一篇文章非常類似，只需要雙擊開啟這個檔案即可，詳情參見：使用Profiler捕獲死鎖。
開啟.trc檔案，截圖如下所示：

這裡提供一種更為簡單便捷，自動化分析方法，程式碼如下：

use master
go

-- declare variables.
declare 
        @file nvarchar(256)
;
select 
        @file = N`C:TempperfmonCHERISH-PC.trc`
;

WITH DATA
AS
(
    -- Analysis deadlock when saving into Trace File
    SELECT 
            --[TraceID] = @trace_id , 
            RowNumber = row_number() OVER (ORDER BY StartTime), -- assign a row number to each deadlock
            [DeadlockGraph]=case when TextData like `<deadlock-list%` then convert(xml, TextData) else null end, 
            * 
    from ::fn_trace_gettable(@file, default)
    where TextData like `<deadlock-list%`
)
,
deadlock
AS
(
        SELECT 
                        RowNumber
                        ,OwnerID = T.C.value(`@id`, `varchar(50)`)
                        ,SPid = T.C.value(`(./@spid)[1]`,`int`)
                        ,status = T.C.value(`(./@status)[1]`,`varchar(10)`)
                        ,Victim = case when T.C.value(`@id`, `varchar(50)`) = T.C.value(`./../../@victim`,`varchar(50)`) then 1 else 0 end
                        ,LockMode = T.C.value(`@lockMode`, `varchar(20)`)
                        ,DeadlockGraph
                        ,Inputbuf = T.C.value(`(./inputbuf/text())[1]`,`varchar(max)`)
                        ,Code = T.C.value(`(./executionStack/frame/text())[1]`,`VARCHAR(max)`)
                        ,SPName = T.C.value(`(./executionStack/frame/@procname)[1]`,`sysname`)
                        ,Hostname = T.C.value(`(./@hostname)[1]`,`sysname`)
                        ,Clientapp = T.C.value(`(./@clientapp)[1]`,`varchar(max)`)
                        ,LoginName = T.C.value(`@loginname`, `varchar(20)`)
                        ,Action = T.C.value(`(./@transactionname)[1]`,`varchar(max)`)
                        --,StartTime
                        ,TransactionTime = T.C.value(`@lasttranstarted`, `datetime`)
                        --,* 
        FROM DATA AS A
                CROSS APPLY DeadlockGraph.nodes(`deadlock-list/deadlock/process-list/process`) AS T(C)
)
,
keylock
AS
(
        SELECT
                OwnerID = T.C.value(`./owner[1]/@id`, `varchar(50)`)
                ,KeylockObject = T.C.value(`./../@objectname`, `varchar(200)`)
                ,Indexname = T.C.value(`./../@indexname`, `varchar(200)`)
                ,IndexLockMode = T.C.value(`./../@mode`, `varchar(20)`)
                --,owner = T.C.query(`.`)
        FROM DATA AS A
                CROSS APPLY DeadlockGraph.nodes(`deadlock-list/deadlock/resource-list/keylock/owner-list`) AS T(C)
)
SELECT 
        --A.OwnerID
        A.SPid
        ,is_Vitim = A.Victim
        --,A.DeadlockGraph
        ,A.SPName
        ,A.Code
        ,A.LockMode
        --,A.StartTime
        ,B.Indexname
        ,B.KeylockObject
        ,B.IndexLockMode
        ,A.Inputbuf
        ,A.Hostname
        ,A.LoginName
        ,A.Clientapp
        ,A.Action
        ,status
        ,A.TransactionTime
FROM deadlock AS A
        LEFT JOIN keylock AS B
        ON A.OwnerID = B.OwnerID
ORDER BY A.RowNumber,A.Victim

執行的結果如下，非常清晰的展示了死鎖的資訊：

最後總結

這篇文章展示的方法相對於前一篇文章，具備了一鍵部署，自動分析，快速結果的強大之處，是解決死鎖問題的利器，希望能夠幫助大家用好，用爽，快速徹底解決SQL Server Deadlock問題。