问题引入

系列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:\Temp\perfmon' ,@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:\Temp\perfmon\CHERISH-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问题。

相关资源：python入门教程(PDF版)