Sql Server Performance Tuning(捅破窗户纸续)

SqlServer的性能问题,也是窗户纸,让偶道来!

 

先考虑一个问题,怎么判断SQL的执行效率是好是坏?也许,95%的人会回答,看执行时间。

 

错!

 

为什么?因为在一个稳定的DB中(稳定这个词,我是这样定义的:某个时间段内,如1周,大部分被SQL缓存的数据是几乎不变的,这意味着客户这段时间内的操作模式、数据变化,是平稳的),同样的sql执行,可能会因为缓存的变化,导致时间变化无常,或者因为一些诸如hot spot,也会导致这个问题。

既然要tuning,就要有一个调优的标准。标准是什么呢?最主要的,看I/O

在一个稳定的DB中,执行同样的SQLI/O基本是不变化的。同样的内存配置,你的台式机,客户的高级server,产生的I/O相差不大的。I/O分为两种,逻辑的,从内存读写;物理的,从磁盘读写。

 

SQL调优的最终目的,就是大幅度的降低I/O大小,减少阻塞,避免死锁。

有了这个目标之后,就可以开始干活了!

首先打开sql analysis(查询分析器),用sa连接到你的数据库,执行

dbcc traceon(1204,3605,-1),这一句保证任何的死锁信息都会被记录在sql log中。

 

然后打开sql profiler(事件探查器),在业务高峰期开始,抓里面的sql completedsp completed,持续2-4个小时(看客户的实际情况而定)。

然后把profiler里面的数据save as到一个table中,加入叫做:jq(偶名字的缩写)。好,到此,成功1/3了!

再次打开查询分析器,执行类似的这条sql

Select textdata, reads, duration from jq order by reads desc

 

这会把所有抓到的sql按照I/O从大到小的顺序排列,睁大你的眼睛,找出来那些I/O最大的,执行最频繁的sqlcopy出来,在查询分析器的另一个窗口中,粘贴上。

哦,先不要急着Ctrl+E,要先执行这个语句!

 

Set statistics io on

然后按一下Ctrl+K(打开执行计划)

 

好了,执行你从jq里面抓到的那个最大的sql吧!仔细分析下面的执行计划,仔细看output中每个表的I/O。分析为什么index的走向不是你所期望的,分析为什么这么多nested loops,分析为什么有大量的worktable?等等,等等。

 

上面是对于普通sql调优的办法。而对于阻塞,可以参考msdn的文章,kbid271509

对于死锁,刚才说过了,只要dbcc traceon(1204,3605,-1)执行后,所有的deadlock都会记录在sql log中。这个日志,纯文本文件,一般会列举出,至少两个对象的当前状态。常见的,如:

KEY: 8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode: U Fl ags: 0x0

KEY: 8:1653632984:1 (2d018af70d80) CleanCnt:1 Mode: X Flags: 0x0

 

通过察看sysobjectsID,和index,我们可以找到对应的deadlocktable,通过分析执行计划,我们可以看出死锁发生的原因。具体内容,参考msdn文章,KBID832524

 

补充一下,GTEC也作SQLcase,虽然收费不菲,哈哈!

(注,连续三篇随笔介绍的情况和方法,同样适用于Vista/SqlServer 2005等最新MS产品)

发表于 @ 15:30 | (阅读:1353;评论:6)

要想解决上文提到的几种常见情况,首先,代码开发人员都要提供相应的dllsymbols。什么叫symbols?就是符号表!有了它,我们可以根据dump,确切的看到问题代码的所处位置:源文件名、方法名、行号等。对于VC++.NET程序,symbols就是后缀为.PDB的那个文件,对于Borland系列的,需要build出来一个.map文件,然后通过Map2Dbg来生成.DBG文件。

这是我们自己的symbols,其实,微软各种产品也有symbols。一部分叫做public的,一部分叫做private的。对于后者,我们拿不到。

symbols准备好了之后,还要下载一个工具,叫做windbg。下载地址,你可以到google.cnsearchdebugging tools for windows,安装就可以了。

有了这两样,我们就可以开始干活了!

 

对于Memory Leak,发现现象之后,分析哪里产生了泄漏,这是一个很难的任务。这里推荐几款工具和文章:

l  XiongLibloghttp://www.cnblogs.com/lixiong 这是GTEC的大牛,偶的哥们+偶像。

l  UMDH工具,安装好上面的windbg,你会在同一个目录下发现这个文件。具体用法,看上面的blog

l  Debug Diag工具,在google中搜索:IIS Diagnostics,到msdn上下载即可。

 

尤其是debug diag,使用非常简单。你用它抓到dump之后,可以自动进行分析,产生格式友好的html文件。UMDH也可以分析,不过使用起来有些罗嗦。

 

对于CPU使用占100%的情况,解决办法是,restart出问题的进程,打开taskmgr,选择该进程,眼睛盯着该进程的CPU变化(注意!眼睛不要眨!!!)。如果CPU持续达到85%以上(经常是100%居多),好,这时候利用上面提到的windbg,打开命令行,运行

adplus –hang –p 1234 –o c:/dumps

具体参数,可以运行adplus来看帮助。

抓好dump之后,可以用debugdiag来分析,也可以用windbg来分析。如果你想锻炼一下windbg的使用,可以按照如下操作来做:

.load clr10/sos.dll

!runaway

敲完这两个命令后,看输出的前几行,记录下来thread id。然后分别用kb来察看,把其中的代码都复制到你的notepad里面。

 

Ok,等一段时间(你自己决定长短),如果该进程CPU还是持续100%,按照上面步骤,继续抓。这样抓好3组后,通过!runaway命令,看占用CPU最高的threadcall stack。如果内容都类似,说明我们抓到了导致hang的代码。下面的工作就是分析这些call stack了。

对于hang,还有一种情况,就是CPUMemory都很低,但是客户端执行就是没有反映。这里举一个我们自己项目的例子:

0x4bc8f458  0x77f88f03 [FRAME: ECallMethodFrame] [DEFAULT] I4 System.Threading.WaitHandle.WaitMultiple(SZArray Class System.Threading.WaitHandle,I4,Boolean,Boolean)
0x4bc8f470  0x26547659 [DEFAULT] I4 System.Threading.WaitHandle.WaitAny(SZArray Class System.Threading.WaitHandle,I4,Boolean)
0x4bc8f484  0x26385470 [DEFAULT] [hasThis] Class System.Data.SqlClient.SqlInternalConnection System.Data.SqlClient.ConnectionPool.GetConnection(ByRef Boolean)
0x4bc8f4b8  0x2758fbad [DEFAULT] Class System.Data.SqlClient.SqlInternalConnection System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(Class System.Data.SqlClient.SqlConnectionString,ByRef Boolean)
0x4bc8f4f8  0x2758f6b9 [DEFAULT] [hasThis] Void System.Data.SqlClient.SqlConnection.Open()
0x4bc8f534  0x2fdd26bc [DEFAULT] [hasThis] Void Genersoft.Focus.Db.DataBase.Open()
  at [+0x7c] [+0x37]

 

从最下面看,那个Database.Open是我们自己的db helper,然后看最上面的红色部分,由一个WaitAny。这个call stack就是我们实际项目中发生的。

通过看中间那个红色加粗部分,我们看到了.net自己的代码,ConnPool.GetConn。通过reflector看这段代码实现,我们知道了.NETdb conn是从pool里面得到的,而pool的大小是有限的。所以,我们猜测:程序中存在着db conn没有Close或者DataReader没有Close的问题。searchi一番,果然如此。修改后,问题解决。

 

对于Access Violation问题(简称AVJ),我们一般也使用adplus进行抓取。命令如下:

adplus –crash –p 1234 –o c:/dumps

 

AV或者Crash分析起来也比较麻烦。如果想偷懒,可以用debug diag自动分析。如果自己手工作,参考上面xiong liblog,哈哈!

这里也举一个我以前做培训时候的例子。

char a[] = "hello";

a[0] = 'X';

printf("%s",a);

char *p = "world";     // 注意p指向常量字符串

p[0] = 'X';            // 编译器不能发现该错误

printf("%s",p);

 

此段代码取自林锐博士的“高质量C/C++编程”,运行后会crash,我们抓了dump之后,按alt+7切换到汇编代码中,发现如下语句:

0040dcac c60058           mov     byte ptr [eax],0x58     ds:0023:0042201c=77

windbg下面运行!address 0042201c,得到如下结果:

0:000> !address 0042201c

    00400000 : 00422000 - 00002000

                    Type     01000000 MEM_IMAGE

                    Protect  00000002 PAGE_READONLY

                    State    00001000 MEM_COMMIT

                    Usage    RegionUsageImage

                    FullPath

 

注意上面的protect属性,该页面是只读的,所以导致了AV错误,这是真正的原因。当然,表面上的解释是,char * p = “world”,这个world存放在.data segment,不能修改。

 

好,讲完了,窗户纸好捅破吧?哈哈!

慢着,如果你没用这种思路来考虑过问题,你一定会看晕的。因为我写的太粗略了,太简单了!这里面涉及到的概念如此之多,涉及到的工具如此之多,经常让我们回忆起操作系统课程的N多东西。

 

想摆脱这种恶梦吗?找GTEC吧,上CASE,那些牛人们会解决的。

 

根据我的经验,上述问题几乎每个项目都会有,但是没有一个人会重视。用C++的,也许对于AV/Crash,彻底麻木了。用.NET的,对于session staterecycled等,也彻底麻木了。“很简单,重新启动机器就好了嘛!”。

是啊!这也是一个解决之道………………

 

(下面接着来做SQL ServerPerformance Tuning,待续)

 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值