915
技術社區[雲棲]
RDS SQL Server死鎖（Deadlock）係列之三自動部署Profiler捕獲死鎖
問題引入
係列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:\Temp\perfmon\CHERISH-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問題。
最後更新：2017-04-24 10:30:40