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

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: # 问题引入 系列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问题。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务,需要仔细地比较和转换数据类型。通过上述方法,可以在两个数据库系统之间建立起相互兼容的数据结构,为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应,还要注意特定数据类型在不同系统中的表现差异,确保数据的一致性和完整性。
35 4
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
3月前
|
关系型数据库 MySQL 数据库
docker启动mysql多实例连接报错Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
docker启动mysql多实例连接报错Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
202 0
|
4月前
|
SQL 关系型数据库 数据库
数据库空间之谜:彻底解决RDS for SQL Server的空间难题
【8月更文挑战第16天】在管理阿里云RDS for SQL Server时,合理排查与解决空间问题是确保数据库性能稳定的关键。常见问题包括数据文件增长、日志文件膨胀及索引碎片累积。利用SQL Server的动态管理视图(DMV)可有效监测文件使用情况、日志空间及索引碎片化程度。例如,使用`sp_spaceused`检查文件使用量,`sys.dm_db_log_space_usage`监控日志空间,`sys.dm_db_index_physical_stats`识别索引碎片。同时,合理的备份策略和文件组设置也有助于优化空间使用,确保数据库高效运行。
86 2
|
4月前
|
数据采集 关系型数据库 MySQL
大数据-业务数据采集-FlinkCDC The MySQL server is not configured to use a ROW binlog_format
大数据-业务数据采集-FlinkCDC The MySQL server is not configured to use a ROW binlog_format
44 1
|
4月前
|
SQL 关系型数据库 MySQL
“震撼揭秘!Flink CDC如何轻松实现SQL Server到MySQL的实时数据同步?一招在手,数据无忧!”
【8月更文挑战第7天】随着大数据技术的发展,实时数据同步变得至关重要。Apache Flink作为高性能流处理框架,在实时数据处理领域扮演着核心角色。Flink CDC(Change Data Capture)组件的加入,使得数据同步更为高效。本文介绍如何使用Flink CDC实现从SQL Server到MySQL的实时数据同步,并提供示例代码。首先确保SQL Server启用了CDC功能,接着在Flink环境中引入相关连接器。通过定义源表与目标表,并执行简单的`INSERT INTO SELECT`语句,即可完成数据同步。
357 1
|
4月前
|
SQL 存储 监控
|
4月前
|
Java 应用服务中间件 Maven
从零到英雄:一步步构建你的首个 JSF 应用程序,揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准,用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets,便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用,包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
100 0
|
4月前
|
SQL 关系型数据库 MySQL
【超全整理】SQL日期与时间函数大汇总会:MySQL与SQL Server双轨对比教学,助你轻松搞定时间数据处理难题!
【8月更文挑战第31天】本文介绍了在不同SQL数据库系统(如MySQL、SQL Server、Oracle)中常用的日期与时间函数,包括DATE、NOW()、EXTRACT()、DATE_ADD()、TIMESTAMPDIFF()及日期格式化等,并提供了具体示例。通过对比这些函数在各系统中的使用方法,帮助开发者更高效地处理日期时间数据,满足多种应用场景需求。
426 0
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
408 0
下一篇
无影云桌面