开发者社区> 风移> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

RDS SQL Server死锁(Deadlock)系列之三自动部署Profiler捕获死锁

简介: # 问题引入 系列SQL Server死锁系列文章之二,讲的是如何手动部署Profiler来捕获死锁以及对死锁发时场景重现,这篇文章是将这个手动部署的过程自动化话,实现一键部署,既快捷方便,又简单适用。上一篇文章,参见:[使用Profiler捕获死锁](https://yq.aliyun.com/articles/73951?spm=5176.8091938.0.0.oDXHeW)。 # 自动
+关注继续查看

问题引入

系列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文件,截图如下所示:
01.png

这里提供一种更为简单便捷,自动化分析方法,代码如下:

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

执行的结果如下,非常清晰的展示了死锁的信息:
02.png

最后总结

这篇文章展示的方法相对于前一篇文章,具备了一键部署,自动分析,快速结果的强大之处,是解决死锁问题的利器,希望能够帮助大家用好,用爽,快速彻底解决SQL Server Deadlock问题。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
[网络篇]ESP8266-SDK教程(三)之TCP通信Server<->Client
纳尼?昨天刚刚打印了个“Hello World!”,今天你就让我学习TCP通信?有没有搞错~哈哈,相信很多读者会很迷,其实学习这东西嘛,单单学一些比较简单的,相信没两天就没人看了,所以咱就在基础篇和网络篇穿插着去学习一下ESP8266,毕竟兴趣才是最好的老师嘛!大家以后遇到问题了,来翻文章建议大家根...
1783 0
CChelper彩虹SDK可视远程客服解决方案
本文讲的是 :  CChelper彩虹SDK可视远程客服解决方案  , 在智能生态产业链中,智能硬件终端是把握消费者的直接环节,随着物联网时代迈向成熟,智能家居领域的硬件逐渐成为智能硬件终端的主角。目前的市场环境下,智能家居领域的自身硬件对于消费者而言非常新颖,且繁多的APPs操作复杂。
2334 0
RDS SQL Server死锁(Deadlock)系列之五利用Extended Events获取死锁信息
# 问题引入 在过去很长一段时间,不断有客人会问道:“在事先没有任何跟踪或者监控部署的情况下,阿里云RDS SQL Server有没有办法获取到历史死锁信息,供我们分析?”。在写到RDS SQL Server死锁系列文章之五时,我们就可以使用Extended Events来解决这个问题。 # 分析问题 Extended Events是微软从SQL Server 2008版本开始引入的,其中有
3236 0
第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁
原文: 第十六章——处理锁、阻塞和死锁(3)——使用SQLServer Profiler侦测死锁 前言: 作为DBA,可能经常会遇到有同事或者客户反映经常发生死锁,影响了系统的使用。
1247 0
SqlServer插入慢的问题解决
这个是我加入的QQ群里面一个网友问的问题。他说一个表做一个INSERT都会超时。正困惑呢,向群里面的人求助。我刚好对这方面知道点,就回答了说要优化聚集索引。他不明白具体该怎么做,似乎也不知道什么是聚集索引。
852 0
SQL Server 2000卸载后重新安装的问题
1.安装SQL Server 2000出现“以前进行的程序安装创建了挂起的文件操作”的解决办法:   a、在开始->运行中输入regedit   b、到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager 位置   ...
687 0
+关注
风移
阿里云数据库专家,负责SQL Server数据库产品线。SQL Server从业10年,经历过SQL 2000、SQL 2005、SQL 2008、SQL 2008R2、SQL 2012、SQL 2014、SQL 2016和SQL on Linux各个版本。
75
文章
46
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载