风移 2016-10-26 5829浏览量
老鸟为了重点栽培菜鸟,决定交给菜鸟一个艰巨而光荣的任务。这天,菜鸟刚到公司还未坐下,老鸟便劈头盖脸的问道:“你知道,我们如何Trace SQL Server执行语句吗?怎么手动分析这些Trace文件?如何将Trace File与Windows的性能监视器结合,看到每个语句执行时的性能开销?以及如何自动分析SQL Server Trace文件?”。
菜鸟还没有反应过来,就被杀死了99%的老细胞,下意识的回答:“啊?”。
“去研究下吧”,这个周给我答复就好了,老鸟看出来了菜鸟的困惑,心理暗自骄傲。
菜鸟怀着一颗万事不着急,先问G哥的平稳心态,开始疯狂的问G哥,如何“手动Trace SQL Server”。G哥开始调动亿万个神经细胞,搜索着散落在地球上每一个角落的问题与答案,哦,原来使用SQL Server Profiler工具:
Start => All Programs => Microsoft SQL Server R2 => Performance Tools => SQL Server Profiler
或者是: Start => Run => Profiler
打开SQL Server Profiler后, File => New Trace => Server type: Database Engine => Server name: XXXX => Login: XXXX => Password: XXXX => Connect
连接完毕以后,设置Trace Properties:
Events Selection => Show all Events => 选择要Trace的事件和字段 => Run。一个简单的Demo长相如下:
Trace启动后,一会儿就有被抓到的语句跑出来:
欧耶,手动部署SQL Server Trace很简单嘛,搞定。菜鸟迫不及待的问G哥,“如何手动部署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
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
Next => Root directory => Finish
性能监视器创建完毕后,最后一个步骤需要启动这个性能监视器,来抓取SQL Server性能指标。选择刚才创建的数据收集器 => 右键点击 => Start。
当菜鸟完成手动搜集Windows性能监视器以后,信心大增。再回过头来问G哥,哪知道G哥早已推荐他:“SQL Server Trace file与性能监视器log文件关联”。G哥是何等聪明绝顶啊。
当SQL Trace文件与性能监视器log文件关联以后,画面为分为四个区域:
菜鸟能够将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
到目前为止,菜鸟已经找到了人生努力的方向,成为高富帅,迎娶白富美是指日可待了。可是菜鸟在分析性能的过程中逐渐意识到,由于查询语句或者存储过程的参数值是“乱七八糟”的,很难做有效的聚合统计。
革命尚未成功,同志还需努力,到底如何实现自动化分析Trace文件呢?菜鸟又想到了G哥,G哥很豪爽并不加思索的说“用RML Utilities for SQL Server啊”。看来现在G哥已是菜鸟的救火队长了,度娘表示很受伤,羡慕滴妒恨。
RML(Replay Markup Language)Utilities是MS SQL Server产品支持服务团队内部开发使用的一个trace文件分析工具。支持SQL Server 2005,2008,2008R2,2012和SQL Server 2014。
主要的功能包括以下几个个方面:
首先从下面的地址下载对应的版本,分为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"
打开RML Command:Start => All programs => RML Utilities for SQL Server => RML Cmd Prompt
执行如下命令:
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输出文件
命令执行完毕后会自动化生成以下的分类报表:
这个报表分别从CPU,Duration,Reads和Writes四个角度统计出查询语句执行次数及所占百分比。当我们查看具体的执行语句的时候(点击处的执行语句),会打开下图中的Unique Statement Details页面。这个页面展示了相应查询语句非常详细的信息,包括:执行语句模版,按照CPU,Duration,Reads和Writes的统计汇总图展示,按照时间顺序的统计汇总表格,格式化后的查询语句。
既然拿到TOP Unique Query语句,接下来的要做的事情就是花80%的时间和精力去优化这20%的TOP Query并加以改善到产品环境,这样我们SQL Server性能问题就可以解决了。
总结来看,RML Utilities for SQL Server是一款与SQL Profiler结合得非常好的自动化Trace文件分析工具。利用这个工具强大的统计汇总功能,使得我们可以非常容易从多角度,多视野,多维度来发现问题,分析问题,解决问题,真正做到工欲善其事必先利其器的效果。
当菜鸟把这份心得呈现在老鸟面前的时候,老鸟简直惊呆了:“不错啊,来,给你十三个赞,每月一个赞”。
“每年不是十二个月吗?”,菜鸟疑惑的问道。
“多的一个是十三薪”,老鸟一边走着一边回答。留着菜鸟一个人在风中凌乱。
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。
分享数据库前沿,解构实战干货,推动数据库技术变革