Sql Server 死锁(Deadlock )分析的方法

最新推荐文章于 2024-06-04 10:16:20 发布
最新推荐文章于 2024-06-04 10:16:20 发布
阅读量921 收藏 3
点赞数
分类专栏： Sql Server 文章标签： 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/baidu_34007305/article/details/109485076
版权

环境

Sql Server 数据库发生死锁。(非紧急情况或紧急情况)

方法

 1. RML Utilities工具+Trace文件捕获死锁
 2. DBCC捕获死锁
 3. Profiler捕获死锁
 4. Trace文件捕获死锁(很耗时在线下执行吧)
 5. 自动部署Profiler捕获死锁
 6. Service Broker事件通知捕获死锁
 7. Extended Events捕获死锁

个人推荐方法1 ，下面将重点介绍它。(其他方法提供互联网链接)

RML Utilities工具+Trace文件捕获死锁

限制：文件必需添加BinaryData列。(本地还需要有数据库，用来存放工具生成的报表)

适用的环境

RML（Replay Markup Language）Utilities是MS SQL Server产品支持服务团队内部开发使用的
一个trace文件分析工具。支持SQL Server 2005，2008，2008R2，2012和SQL Server 2014。
主要的功能包括以下几个个方面：
1.分析最消耗SQL Server系统资源的应用和查询
2.去除参数干扰，统计汇总查询性能消耗
3.生成可视化报表，性能消耗大户一目了然

安装

首先从下面的地址下载对应的版本，分为X86的32位版和64位版：
X86：[https://www.microsoft.com/en-us/download/details.aspx?id=8161]
X64：[https://www.microsoft.com/en-us/download/details.aspx?id=4511]
RML安装文件(RMLSetup_AMD64.msi)是基于Windows MSI的文件。
安装完毕后，RML的默认安装目录会在"C:Program FilesMicrosoft CorporationRMLUtils"

github下载链接
link
https://github.com/microsoft/SqlNexus/releases/download/09.04.0097/RMLSetup_AMD64.msi

使用方法

打开RML Command：Start => All programs => RML Utilities for SQL Server => RML Cmd Prompt
在这里插入图片描述

执行如下命令：（离线分析，需要sql server数据库配合）

#以下代码建议复制到本地进行格式调整之后再使用
ReadTrace -I"C:\Temp\perfmon\XXX.trc" -o"C:\Temp\OUTPUT" -S"." -d"PerfAnalysis" -E -f
LT测试：
ReadTrace -I"G:\tracefiles\Trace_CSC5173_CSC51732008_20201018A.trc" 
-o"G:\tracefiles\Temp\OUTPUT" -S"." -d"PerfAnalysis" -E -f
(LT：如果本地没有sql server数据库，需要连接远程的（测试服务器）)
ReadTrace -I"G:\tracefiles\Trace_CSC5173_CSC51732008_20201018A.trc" 
-o"G:\tracefiles\Temp\OUTPUT"  
-S"192.168.66.128" -d"PerfAnalysis" -E -U"sa2" -P"1qaz!QAZ" -f

有时候你需要确保以上代码正确的情况下，可以双击trace文件，用profiler工具（SSMS工具自带的软件打开），用来验证trace文件是否损坏
在这里插入图片描述

RML分析时是在本地生成了一个数据库，默认是PerfAnalysis， 并将trace文件的数据标准化之后导入数据库，为报表展示提供数据也可以本机连接至PerfAnalysis库， 运用存储过程进行分析。

参数说明

Readtrace的参数是区分大小写的，并且输入文件和输出目录不能够在同一个目录下。
详情参见Readtrace -?的参数描述，这条语句的参数说明：
-I: 输入的Trace文件目录和文件名
-o: 日志文件输出目录
-S: 数据存放的数据库服务器
-d:  --指定数据库（用与存放分析的数据），未指定会创建PerfAnalysis数据库
-E: -- 登录数据库服务器为windows授权模式
-f: 不用生成每一个会话和请求的详细RML输出文件
-U --登录数据库服务器的用户
-P --登录数据库服务器的密码
-a -- 禁用执行分析

查看报表

 上面的完成后，会自动打开Reporter应用程序

``
在这里插入图片描述

Performance Overview
Performance Overview分类报表包括其他分类报表的入口，资源使用率的统计，性能总览的详细信息。
Application Name
按照应用程序名称做分类统计，这个统计报表可以知道每个应用程序对SQL Server系统资源的消耗情况。利用这个报表，我们很轻松就可以找到资源使用的大户，针对性的采取必要的措施。
(以下显示的就是应用程序的资源消耗，一般也就是这个了)
在这里插入图片描述
Unique Batches
这个分类报表非常有价值，在这个报表中，我们非常轻松的就可以发现占用CPU，Run Duration，IO Read，IO Write TOP Batches语句块。这个为我们优化具体的SQL语句块提供了非常方便的统计汇总。
在这里插入图片描述
Interesting Events
针对SQL Server数据库事件的统计分类报表。
（LT:无）
Database ID
select * from master.dbo.sysdatabases 通过DBID查找对应的数据库

按照数据库标识ID的分类报表，从这个报表，我们可以很容易发现哪个数据库的压力比较大，可以选择作为我们重点优化的数据库。
在这里插入图片描述

（重点关注的地方）
例如，点击"Database Id"，就可以得到在Trace文件所包含的那个时间段，所有曾经访问过SQL Server的
数据库，它们总共发了多少条语句(Started/Completed)，有过几次超时(Attentions)，
总共花在SQL Server里的时间是多少(Duration)，CPU的时间是多少，
Reads/Writes各是多少。点击这些栏位边上的小箭头，可以按这个栏位排序。

在这里插入图片描述
Unique Statements
这个与Unique Batches分类统计报表类似，也是非常有价值的报表，只不过这个是针对特定语句的分类统计报表，而不是针对语句块，所以，这两个报表的分析方式非常类似。
这个报表分别从CPU，Duration，Reads和Writes四个角度统计出查询语句执行次数及所占百分比。点击"Unique Batches"，就可以得到一个关于Batch级别的报表。Batch级别的报表针对的是存储过程或是一个TSQL Batch，存储过程或Batch内部的TSQL语句不会单独列在该报表上。很多存储过程调用或TSQL Batch语句总体是一样的，只是带的参数会有不同，ReadTrace会把这些仅是参数不同的存储过程或语句归为一类。这样，如果一个存储过程可能每次执行并不是很长时间，但是它在SQL Server里被很频繁地调用。这个工具也能按它的总调用时间把它找出来：例如按Duration排序，列出总Duration最多的语句：
在这里插入图片描述
Data Lineage
这个报表是Readtrace数据导入的日志信息统计，包含你使用的参数信息和RML的错误警告信息等。比如，这里就提示，我们的SQL Server Trace文件少了SP:StmtStarting事件的跟踪。

Login Name
按照登录用户聚合的分类统计报表，从这报表我们可以很容易发现哪些用户是数据库系统资源的大户。

找到数据库

``
可以看出第二条语句是GSP_GetWeixinBindsPagedWithAccount被调用1222次，总耗时1768295276(µs)，我们可以直接用SQL语句查看此过程执行情况：

select 'DBName' DatabaseName,count(*) [count],sum(CPU_ms) 
sum_CPU_ms,avg(CPU_ms) avg_CPU_ms,sum(Duration_ms)
 sum_Duration_ms,avg(Duration_ms) avg_Duration_ms
,sum(Reads) sum_Reads,avg(Reads) avg_Reads
,sum(Writes) sum_Writes,avg(Writes) avg_Writes
from(
select convert(varchar(max),TextData) TextData,DatabaseName,Duration/1000.0 
Duration_ms,CPU CPU_ms,Reads,Writes,StartTime,EndTime,LoginName,ApplicationName
from fn_trace_gettable(N'F:\TroubleShooting\Trace\HighDuration20151212.trc', default)
where TextData is not null
and textdata like '%KeyWords%'
) a
group by left(TextData,1),DatabaseName
order by left(TextData,1)

where条件中的KeyWords用对应存储过程替换，执行结果如下：
在这里插入图片描述

在我们运行导入命令后，数据处理完会自动打开Reporter应用程序。那电脑关闭后，
下次要查看之前的数据怎么办呢，
难道又得重新运行命令？完全不必要，第一次导入时已经把相关数据保存到对应的数据库中，
我们可以在开始->所有程序->RML Utilities for SQL Server->Reporter，
只要设置好连接，点击OK就会打开报表：
（LT:只支持显示一个文件的报表）

在这里插入图片描述
参考链接
link
https://developer.aliyun.com/article/62333
link
https://www.cnblogs.com/Uest/p/5046576.html

前文提到的方法链接
DBCC捕获死锁 https://developer.aliyun.com/article/73856
link
Profiler捕获死锁 https://developer.aliyun.com/article/73951
link
Profiler捕获死锁 https://developer.aliyun.com/article/74391
link
Service Broker事件捕获死锁 (前提需要建立一个检测死锁的库DeadlockCapture
(比较耗时)) 链接（略），可在以上文档中找到
Extended Events捕获死锁 https://developer.aliyun.com/article/74629
link

Trace文件 捕获死锁 (可以捕获目前的状态（不推荐，消耗系统资源），或是打开之前生成的trace)
在这里插入图片描述
死锁测试代码(参考)
https://developer.aliyun.com/article/73856
link

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​

请给我的爱人一杯mojito
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
SQL死锁产生原因和解决方法
qq_27439819的博客
03-15 6856
个人博客 死锁现象 表现一：一个用户A 访问表A(锁住了表A)，然后又访问表B，另一个用户B 访问表B(锁住了表B)，然后企图访问表A，这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B，才能继续，好了他老人家就只好老老实实在这等了，同样用户B要等用户A释放表A才能继续这就死锁了。 解决方法 这种死锁是由于你的程序的BUG产生的，除了调整你的程序的逻辑别无他法 　　仔细分析你程序的逻辑：...
Sql Server 死锁的监控分析解决思路
09-09
本篇文章将深入探讨SQL Server死锁的监控、分析以及解决思路。 1. **死锁监控** SQL Server提供了一种名为`sys.dm_tran_deadlock_sessions`的动态管理视图（DMV），用于实时监控死锁情况。通过查询这个视图，你...
SQL Server死锁产生原因及解决办法
拼命微笑的专栏
09-27 6817
SQL Server死锁产生原因及解决办法 其实所有的死锁最深层的原因就是一个：资源竞争 表现一： 　　一个用户A 访问表A(锁住了表A)，然后又访问表B，另一个用户B 访问表B(锁住了表B)，然后企图访问表A，这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B，才能继续，好了他老人家就只好老老实实在这等了，同样用户B要等用户A释放表A才能继续这就死锁了。 　　
捕抓和记录SQL发生的死锁信息
阿达的博客
03-19 561
 我们知道，可以使用SQL Server自带的Profiler工具来跟踪死锁信息。但这种方式有一个很大的敝端，就是消耗很大。据国外某大神测试，profiler甚至可以占到服务器总带宽的35%，所以，在一个繁忙的系统中，使用profiler显然不是一个好主意，下面我介绍两种消耗比较少的方法。其中第二种的消耗最小，在最繁忙的系统中也可使用。第一种最为灵活，可满足多种应用。 方法一：利用SQ
SQL Server 数据库死锁处理
最新发布
m0_74824913的博客
06-04 1352
SQL Server数据库死锁是指在多个事务同时访问数据库资源时，发生了互相等待对方所持有资源的情况，导致所有事务无法继续执行的现象。当发生死锁时，SQL Server会自动选择一个事务进行回滚，以解除死锁并允许其他事务继续执行。这篇文章为个人学习笔记，可能会有错误。
SQL Server DeadLock 分析
11-27 613
【代码】SQL Server 死锁 分析。
SQL Server死锁跟踪常用方法
Hehuyi_In的博客
09-20 8374
一、利用windows计数器 下面的查询提供了自从上次重启以来在本服务器上发生的所有死锁： SELECT cntr_value AS NumOfDeadLocks FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Locks' AND counter_name = 'Number of Deadl...
sqlserver进程死锁关闭的方法
09-10
- 使用死锁探测和监控：SQL Server提供死锁图形日志（Deadlock Graphs）和死锁监控器（sys.dm_os_wait_stats），通过定期分析这些信息，可以发现可能导致死锁的模式并进行调整。 - 使用死锁超时设置：通过设置` ...
sql server deadlock跟踪的4种实现方法
09-09
本文将详细介绍四种在SQL Server中跟踪死锁的方法。 1. **使用Trace Log跟踪** SQL Server允许我们启用特定的跟踪标志（如1204和1222）来记录死锁信息。要启用这些标志，可以在查询窗口中执行以下命令： ```sql ...
SqlServer表死锁的解决方法分享
09-11
本文将深入探讨SQL Server中的表死锁问题及其解决方法。 首先，我们要理解死锁的基本概念。死锁发生时，两个或更多事务各自持有对方需要的资源，导致它们都无法继续执行。例如，在存储过程中，如果事务A持有一张表...
SQL Server 查询死锁sql
q370786406的博客
10-09 751
SELECT 标志, 进程 ID = spid, 线程 ID = kpid, 块进程 ID = blocked, 数据库 ID = dbid, 数据库名 = db_name( dbid ), 用户 ID = uid, 用户名 = loginame, 累计 CPU时间 = cpu, 登陆时间 = login_time, 打开事务数 = open_tran, 进程状态 = status, 工作站名 = hostname, 应用程序名 = program_name, 工作站进程 I.
SQL Server上的一个奇怪的Deadlock及其分析方法
03-24
SQL Server上的一个奇怪的Deadlock及其分析方法
sqlserver 解除死锁
11-22
sqlserver 解除死锁，网络上看到的很实用分享给大一起用
sql数据库死锁查询工具
02-27
数据库死锁，阻塞查询，杀死死锁，阻塞进程
SqlServer数据库死锁排查方法
niu_mo_wang的专栏
07-08 6336
1. 开启数据库死锁日志功能 1.1 在SQLSERVER企业管理器里执行下面 a 命令,开启死锁日志 --a. 开启跟踪死锁 DBCC TRACEON(3605,1204,1222,-1) --b. 关闭信号跟踪 DBCC TRACEOFF(1222,-1) DBCC TRACEOFF(1204,-1) --c.查看信号跟踪是否开启。 DBCC TRACESTATUS(1204,-1) DBCC TRACESTATUS(1222,-1) 1.2 执行命令后，如果发生死锁，可以在数据库日志 或者.
解决sqlserver死锁问题
leihenshang的博客
12-06 7574
创造死锁 -- 开始事务 BEGIN TRANSACTION update job set create_user='0000' where id = '1' WAITFOR DELAY '02:00' -- 执行查询 select * from job where id = '1'; 方法一 打开sqlserver管理工具，新建sql查询tab 执行select * from master.sys.sysprocesses where dbid=db_id(‘数据库名’) 然后会查询到具体有哪
SQL Server中如何监控死锁（Deadlock）
weixin_34405332的博客
01-07 340
SQL Server中如何监控死锁（Deadlock） 什么是死锁？所谓死锁： 是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。 由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，...
SqlServer死锁com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 52) was deadlock
请大家直接把问题写在评论区或留言，不要只说一句"你好 或 在吗"，我会尽快回复的。
01-10 2320
Java多线程操作SqlServer数据库，执行update的操作 ；报错： com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the dead...
sqlserver 死锁杀不掉
07-09
死锁是在数据库系统中常见的问题，它发生在两个或多个事务之间，每个事务都在等待其他事务释放资源。当发生死锁时，数据库系统会自动检测到并尝试解决死锁。但有时候，数据库系统可能无法自动解决死锁，需要手动介入来解决问题。 要解决死锁问题，可以尝试以下方法： 1. 确认是否是死锁：使用 SQL Server Management Studio (SSMS) 或其他监控工具来检测数据库是否存在死锁。可以查看系统视图sys.dm_tran_locks和sys.dm_exec_requests来获取相关信息。 2. 了解死锁的原因：查看死锁报告（Deadlock Graph）以及相关日志，了解死锁的原因和涉及的事务。这有助于理解死锁发生的原因和模式。 3. 优化事务：优化事务的设计和逻辑，尽量减少事务持有锁的时间。确保事务只在必要时持有锁，并尽快释放。 4. 调整隔离级别：根据业务需求和性能要求，选择合适的事务隔离级别。较低的隔离级别（如读已提交）可以减少死锁的发生，但也可能导致读取到脏数据。 5. 添加索引：通过添加适当的索引来提高查询性能，减少锁竞争的机会。 6. 使用锁提示：在需要的地方使用锁提示（如NOLOCK、READPAST），可以减少锁竞争的可能性。但需要注意，过度使用锁提示可能导致脏读和不可重复读的问题。 7. 分析和优化查询计划：使用SQL Server的查询优化工具，如SQL Server Profiler和Database Engine Tuning Advisor，分析和优化查询计划，以减少锁竞争。 8. 调整数据库配置：根据数据库的负载和硬件配置，适当调整数据库的参数设置，如最大并行度、最大内存限制等。 如果以上方法无法解决死锁问题，可以考虑手动干预来解决死锁。可以使用以下方法之一： - 杀死被占用资源的事务：使用KILL命令来终止占用资源的事务。但需要谨慎操作，确保终止的事务不会引起数据损坏或其他问题。 - 重启数据库：如果死锁问题无法解决，可以考虑重启数据库实例。这将清空所有的锁和事务，但也会导致数据库不可用一段时间。 需要注意的是，在手动解决死锁问题时，应该在非生产环境中进行测试和验证，以避免潜在的数据丢失或其他风险。另外，建议定期监控数据库系统，及时发现和解决潜在的死锁问题。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值