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