分享
 
 
 

应用Profiler优化SQLServer数据库系统

王朝mssql·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

概述

当你的SQL Server数据库系统运行缓慢的时候,你或许多多少少知道可以使用SQL Server Profiler(中文叫SQL事件探查器)工具来进行跟踪和分析。是的,Profiler可以用来捕获发送到SQL Server的所有语句以及语句的执行性能相关数据(如语句的read/writes页面数目,CPU的使用量,以及语句的duration等)以供以后分析。但本文并不介绍如何使用Profiler 工具,而是将介绍如何使用read80trace(有关该工具见后面介绍)工具结合自定义的存储过程来提纲挈领地分析Profiler捕获的Trace文件,最终得出令人兴奋的数据分析报表,从而使你可以高屋建瓴地优化SQL Server数据库系统。

本文对那些需要分析SQL Server大型数据库系统性能的读者如DBA等特别有用。在规模较大、应用逻辑复杂的数据库系统中Profiler产生的文件往往非常巨大,比如说在Profiler中仅仅配置捕获基本的语句事件,运行二小时后捕获的Trace文件就可能有GB级的大小。应用本文介绍的方法不但可以大大节省分析Trace的时间和金钱,把你从Trace文件的海量数据中解放出来,更是让你对数据库系统的访问模式了如指掌,从而知道哪一类语句对性能影响最大,哪类语句需要优化等等。

Profiler trace文件性能分析的传统方法以及局限

先说一下什么是数据库系统的访问模式。除了可以使用Trace文件解决如死锁,阻塞,超时等问题外,最常用也是最主要的功能是可以从Trace文件中得到如下三个非常重要的信息:

1.运行最频繁的语句

2.最影响系统性能的关键语句

3.各类语句群占用的比例以及相关性能统计信息

本文提到的访问模式就是上面三个信息。我们知道,数据库系统的模块是基本固定的,每个模块访问SQL Server的方式也是差不多固定的,具体到某个菜单,某个按钮,都是基本不变的,所以,在足够长的时间内,访问SQL Server的各类语句及其占用的比例也基本上是固定的。换句话说,只要Profiler采样的时间足够长(我一般运行2小时以上),那么从Trace文件中就肯定可以统计出数据库系统的访问模式。每一个数据库系统都有它自己独一无二的访问模式。分析Profiler Trace文件的一个重要目标就是找出数据库系统的访问模式。一旦得到访问模式,你就可以在优化系统的时候做到胸有成竹,心中了然。可惜直到目前为止还没有任何工具可以方便地得到这些信息。

传统的Trace分析方法有两种。一种是使用Profiler工具本身。比如说可以使用Profiler的Filter功能过滤出那些运行时间超过10秒以上的语句,或按照CPU排序找出最耗费CPU的语句等。另一种是把Trace文件导入到数据库中,然后使用T-SQL语句来进行统计分析。这两种方法对较小的Trace文件是有效的。但是,如果Trace文件数目比较多比较大(如4个500MB以上的trace文件),那么这两种方法就有很大的局限性。其局限性之一是因为文件巨大的原因,分析和统计都非常不易,常常使你无法从全局的高度提纲挈领地掌握所有语句的执行性能。你很容易被一些语句迷惑而把精力耗费在上面,而实际上它却不是真正需要关注的关键语句。局限性之二是你发现尽管很多语句模式都非常类似(仅仅是执行时参数不同),却没有一个简单的方法把他们归类到一起进行统计。简而言之,你无法轻而易举地得到数据库系统的访问模式,无法在优化的时候做到高屋建瓴,纲举目张。这就是传统分析方法的局限性。使用下面介绍的Read80trace工具以及自定义的存储过程可以克服这样的局限性。

Read80trace工具介绍以及它的Normalization 功能

Read80Trace工具是一个命令行工具。使用Read80Trace工具可以大大节省分析Trace文件的时间,有事半功倍的效果。Read80Trace的主要工作原理是读取Trace文件,然后对语句进行Normalize (标准化),导入到数据库,生成性能统计分析的HTML页面。另外,Read80trace可以生成RML文件,然后OSTRESS工具使用RML文件多线程地重放Trace文件中的所有事件。这对于那些想把Profiler捕获的语句在另外一台服务器上重放成为可能。本文不详细介绍Read80trace或OStress工具,有兴趣的读者请自行参阅相关资料,相关软件可以从微软网站下载(注:软件名称为RML,http://www.microsoft.com/downloads/)。

我要利用的是Read80Trace的标准化功能。什么是标准化?就是把那些语句模式类似,但参数不一样的语句全部归类到一起。举例说Trace中有几条语句如下:

select * from authors where au_lname = 'white'

select * from authors where au_lname = 'green'

select * from authors where au_lname = 'carson'

经过标准化后,上面的语句就变成如下的样子:

select * from authors where au_lname = {str}

select * from authors where au_lname = {str}

select * from authors where au_lname = {str}

有了标准化后的语句,统计出数据库系统的访问模式就不再是难事。运行Read80trace 的时候我一般使用如下的命令行:

Read80trace ?f ?dmydb ?Imytrace.trc

其中-f开关是不生成RML文件,因为我不需要重放的功能。生成的RML文件比较大,建议读者如果不需要重放的话,也使用-f开关。

-d开关告诉read80trace把trace文件的处理结果存到mydb数据库中。我们后面创建的存储过程正是访问read80trace在mydb中生成的表来进行统计的。-I开关是指定要分析的的trace文件名。Read80trace工具很聪明,如果该目录下有Profiler产生的一系列Trace文件,如mytrace.trc,mytrace1.trc,mytrace2.trc等,那么它会一一顺序读取进行处理。

除了上面介绍的外,Read80trace还有很多其它有趣的开关。比如说使用-i开关使得Read80trace可以从zip或CAB文件中读取trace文件,不用自己解压。所有开关在Read80trace.chm中有详细介绍。我最欣赏的地方是read80trace的性能。分析几个GB大小的trace文件不足一小时就搞定了。我的计算机是一台内存仅为512MB的老机器,有这样的性能我很满意。

你也许会使用read80trace分析压力测试产生的trace文件。我建议还是分析从生产环境中捕获的Trace文件为好。因为很多压力测试工具都不能够真正模拟现实的环境,其得到的trace文件也就不能真实反映实际的情况。甚至有些压力测试工具是循环执行自己写的语句,更不能反映准确的访问模式。建议仅仅把压力测试产生的trace作为参考使用。

使用存储过程分析Normalize后的数据

有了标准化后的语句就可以使用存储过程进行统计分析了。分析的基本思想是把所有模式一样的语句的Reads,CPU和Duration做group by统计,得出访问模式信息:

1.某类语句的总共执行次数,平均读页面数(reads)/平均CPU时间/平均执行时间等。

2.该类语句在所有语句的比例,如执行次数比例,reads比例,CPU比例等。

存储过程的定义以及说明如下:

/*************************************************************/

Create procedure usp_GetAccessPattern 8000

@duration_filter int=-1 --传入的参数,可以按照语句执行的时间过滤统计

as begin

/*首先得到全部语句的性能数据的总和*/

declare @sum_total float,@sum_cpu float,@sum_reads float,@sum_duration float,@sum_writes float

select @sum_total=count(*)*0.01,--这是所有语句的总数。

@sum_cpu=sum(cpu)*0.01, --这是所有语句耗费的CPU时间

@sum_reads=sum(reads)*0.01, --这是所有语句耗费的Reads数目,8K为单位。

@sum_writes=sum(writes)*0.01,--这是所有语句耗费的Writes数目,8K为单位。

@sum_duration=sum(duration)*0.01--这是所有语句的执行时间总和。

from tblBatches --这是Read80Trace产生的表,包括了Trace文件中所有的语句。

where duration=@duration_filter --是否按照执行时间过滤

/*然后进行Group by,得到某类语句占用的比例*/

Select ltrim(str(count(*))) exec_stats,''+ str(count(*)/@sum_total,4,1)+'%' ExecRatio,

ltrim(str(sum(cpu)))+' : '++ltrim(str(avg(cpu))) cpu_stats,''+str(sum(cpu)/@sum_cpu,4,1)+'%' CpuRatio,

ltrim(str(sum(reads) ))+' : '+ltrim(str(avg(reads) )) reads_stats,''+str(sum(reads)/@sum_reads,4,1)

+'%' ReadsRatio ,

--ltrim(str(sum(writes) ))+' : '+ltrim(str(avg(writes) )) --writes_stats,''+str(sum(writes)/@sum_writes,4,1) +'%)',

ltrim(str(sum(duration) ))+' : '+ltrim(str(avg(duration))) duration_stats,''+str(sum(duration)/@sum_duration,4,1)+'%' DurRatio ,

textdata,count(*)/@sum_total tp,sum(cpu)/@sum_cpu cp,sum(reads)/@sum_reads rp,sum(duration)/@sum_duration dp

into #queries_staticstics from

/* tblUniqueBatches表中存放了所有标准化的语句。*/

(select reads,cpu,duration,writes,convert(varchar(2000),NormText)textdata from tblBatches

inner join tblUniqueBatches on tblBatches.HashId=tblUniqueBatches.hashid where duration@duration_filter

) B group by textdata --这个group by很重要,它对语句进行归类统计。

print 'Top 10 order by cpu+reads+duration'

select top 10 * from #queries_staticstics order by cp+rp+dp desc

print 'Top 10 order by cpu'

select top 10

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有