如何分析SQL Server Trace文件-阿里云开发者社区

开发者社区> 数据库> 正文

如何分析SQL Server Trace文件

简介: # 1.问题引出 老鸟为了重点栽培菜鸟,决定交给菜鸟一个艰巨而光荣的任务。这天,菜鸟刚到公司还未坐下,老鸟便劈头盖脸的问道:“你知道,我们如何Trace SQL Server执行语句吗?怎么手动分析这些Trace文件?如何将Trace File与Windows的性能监视器结合,看到每个语句执行时的性能开销?以及如何自动分析SQL Server Trace文件?”。 菜鸟还没有反应过来,就被

1.问题引出

老鸟为了重点栽培菜鸟,决定交给菜鸟一个艰巨而光荣的任务。这天,菜鸟刚到公司还未坐下,老鸟便劈头盖脸的问道:“你知道,我们如何Trace SQL Server执行语句吗?怎么手动分析这些Trace文件?如何将Trace File与Windows的性能监视器结合,看到每个语句执行时的性能开销?以及如何自动分析SQL Server Trace文件?”。

菜鸟还没有反应过来,就被杀死了99%的老细胞,下意识的回答:“啊?”。

“去研究下吧”,这个周给我答复就好了,老鸟看出来了菜鸟的困惑,心理暗自骄傲。

2.手动Trace SQL Server

菜鸟怀着一颗万事不着急,先问G哥的平稳心态,开始疯狂的问G哥,如何“手动Trace SQL Server”。G哥开始调动亿万个神经细胞,搜索着散落在地球上每一个角落的问题与答案,哦,原来使用SQL Server Profiler工具:

Start => All Programs => Microsoft SQL Server R2 => Performance Tools => SQL Server Profiler
2_1
或者是: Start => Run => Profiler

打开SQL Server Profiler后, File => New Trace => Server type: Database Engine => Server name: XXXX => Login: XXXX => Password: XXXX => Connect
2_2
连接完毕以后,设置Trace Properties:

Events Selection => Show all Events => 选择要Trace的事件和字段 => Run。一个简单的Demo长相如下:
2_3
Trace启动后,一会儿就有被抓到的语句跑出来:
2_4
欧耶,手动部署SQL Server Trace很简单嘛,搞定。菜鸟迫不及待的问G哥,“如何手动部署Windows性能监视器”。

3.部署Windows 性能监视器

G哥心想,这两个是关联问题吧,早知道你要问这个问题了,早已在你问第一个问题时,已经帮你做了最佳推荐:

Start => Run => Perfmon => User Defined => right click on the right side blank space => New => Data Collector Set => type Name: PERFMON_BASE => Create manually(Advanced) => Next
3_1
Create data logs => Performance counter => Event trace data => Next => Select counters from computer => 展开性能指标分类,比如:SQL Server:Buffer Manager => Page read/sec, page writes/sec等 => Add => OK
3_2
Next => Root directory => Finish
3_3
性能监视器创建完毕后,最后一个步骤需要启动这个性能监视器,来抓取SQL Server性能指标。选择刚才创建的数据收集器 => 右键点击 => Start。
3_4

4.Trace文件关联性能监视器log文件

当菜鸟完成手动搜集Windows性能监视器以后,信心大增。再回过头来问G哥,哪知道G哥早已推荐他:“SQL Server Trace file与性能监视器log文件关联”。G哥是何等聪明绝顶啊。
4_1
当SQL Trace文件与性能监视器log文件关联以后,画面为分为四个区域:

  • 查询语句区域:查看和选择查询语句
  • 性能指标做图区域:可以看到选中的查询语句对应的各项性能指标做图(性能指标做图区域中红色的竖线)
  • 性能指标选择区域:性能指标选择区域选中或者取消相关的性能指标
  • 查询语句显示区域:查看被选中的相关查询语句详情
    4_2

5.手动分析Trace文件

菜鸟能够将SQL Server Trace文件与性能监视器log文件关联在一起分析,是巨大的进步啊。可是,他还是不满足于现状,觉得一个个去点击查询语句,太过于原始,效率很难提升,而且不方便过滤出自己关心的查询语句。比如过滤出CPU占用最高的TOP 5查询;I/O最高的TOP 5查询;执行最为频繁的查询语句TOP 5等。

于是菜鸟想到了将SQL Server Trace File文件通过系统函数将其读出来,存到一个表里面。这样,就可以利用数据库强大的筛选,过滤,聚合功能得到自己关心的查询语句了。

declare
    @trace_file_path sysname
;

select
    @trace_file_path = N'C:\Temp\perfmon\XXXX.trc'
;

SELECT *
FROM sys.fn_trace_gettable(@trace_file_path, NULL) AS T

到目前为止,菜鸟已经找到了人生努力的方向,成为高富帅,迎娶白富美是指日可待了。可是菜鸟在分析性能的过程中逐渐意识到,由于查询语句或者存储过程的参数值是“乱七八糟”的,很难做有效的聚合统计。

6.自动化分析Trace文件

革命尚未成功,同志还需努力,到底如何实现自动化分析Trace文件呢?菜鸟又想到了G哥,G哥很豪爽并不加思索的说“用RML Utilities for SQL Server啊”。看来现在G哥已是菜鸟的救火队长了,度娘表示很受伤,羡慕滴妒恨。

6.1.RML Utilities功能介绍

RML(Replay Markup Language)Utilities是MS SQL Server产品支持服务团队内部开发使用的一个trace文件分析工具。支持SQL Server 2005,2008,2008R2,2012和SQL Server 2014。

主要的功能包括以下几个个方面:

  • 分析最消耗SQL Server系统资源的应用和查询
  • 去除参数干扰,统计汇总查询性能消耗
  • 生成可视化报表,性能消耗大户一目了然

6.2.RML Utilities安装

首先从下面的地址下载对应的版本,分为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"

6.3.使用方法

打开RML Command:Start => All programs => RML Utilities for SQL Server => RML Cmd Prompt
6_3_1

执行如下命令:

ReadTrace -I"C:\Temp\perfmon\XXX.trc" -o"C:\Temp\OUTPUT" -S"." -d"PerfAnalysis" -E -f

Readtrace的参数是区分大小写的,并且输入文件和输出目录不能够在同一个目录下。详情参见Readtrace -?的参数描述,这条语句的参数说明:

-I: 输入的Trace文件目录和文件名

-o: 日志文件输出目录

-S: 数据存放的数据库服务器

-d: 数据库名字

-E: Windows认证模式链接数据库

-f: 不用生成每一个会话和请求的详细RML输出文件

命令执行完毕后会自动化生成以下的分类报表:

  • Performance Overview
    Performance Overview分类报表包括其他分类报表的入口,资源使用率的统计,性能总览的详细信息。

6_3_2

  • Application Name
    按照应用程序名称做分类统计,这个统计报表可以知道每个应用程序对SQL Server系统资源的消耗情况。利用这个报表,我们很轻松就可以找到资源使用的大户,针对性的采取必要的措施。

6_3_3

  • Unique Batches
    这个分类报表非常有价值,在这个报表中,我们非常轻松的就可以发现占用CPU,Run Duration,IO Read,IO Write TOP Batches语句块。这个为我们优化具体的SQL语句块提供了非常方便的统计汇总。

6_3_4

  • Interesting Events
    针对SQL Server数据库事件的统计分类报表。

6_3_5

  • Database ID
    按照数据库标识ID的分类报表,从这个报表,我们可以很容易发现哪个数据库的压力比较大,可以选择作为我们重点优化的数据库。

6_3_6

  • Unique Statements
    这个与Unique Batches分类统计报表类似,也是非常有价值的报表,只不过这个是针对特定语句的分类统计报表,而不是针对语句块,所以,这两个报表的分析方式非常类似。

这个报表分别从CPU,Duration,Reads和Writes四个角度统计出查询语句执行次数及所占百分比。当我们查看具体的执行语句的时候(点击处的执行语句),会打开下图中的Unique Statement Details页面。这个页面展示了相应查询语句非常详细的信息,包括:执行语句模版,按照CPU,Duration,Reads和Writes的统计汇总图展示,按照时间顺序的统计汇总表格,格式化后的查询语句。
既然拿到TOP Unique Query语句,接下来的要做的事情就是花80%的时间和精力去优化这20%的TOP Query并加以改善到产品环境,这样我们SQL Server性能问题就可以解决了。
6_3_7

  • Data Lineage
    这个报表是Readtrace数据导入的日志信息统计,包含你使用的参数信息和RML的错误警告信息等。比如,这里就提示,我们的SQL Server Trace文件少了SP:StmtStarting事件的跟踪。

6_3_8

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

6_3_9

总结来看,RML Utilities for SQL Server是一款与SQL Profiler结合得非常好的自动化Trace文件分析工具。利用这个工具强大的统计汇总功能,使得我们可以非常容易从多角度,多视野,多维度来发现问题,分析问题,解决问题,真正做到工欲善其事必先利其器的效果。

7.写在最后

当菜鸟把这份心得呈现在老鸟面前的时候,老鸟简直惊呆了:“不错啊,来,给你十三个赞,每月一个赞”。

“每年不是十二个月吗?”,菜鸟疑惑的问道。

“多的一个是十三薪”,老鸟一边走着一边回答。留着菜鸟一个人在风中凌乱。

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

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章