Sql Server Performance Tuning（捅破窗户纸续）

原址：http://blog.joycode.com/juqiang

Sql Server Performance Tuning（捅破窗户纸续2）

SqlServer的性能问题，也是窗户纸，让偶道来！

 

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

 

错！

 

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

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

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

 

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

有了这个目标之后，就可以开始干活了！

首先打开sql analysis（查询分析器），用sa连接到你的数据库，执行

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

 

然后打开sql profiler（事件探查器），在业务高峰期开始，抓里面的sql completed和sp 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最大的，执行最频繁的sql，copy出来，在查询分析器的另一个窗口中，粘贴上。

哦，先不要急着Ctrl+E，要先执行这个语句！

 

Set statistics io on

然后按一下Ctrl+K（打开执行计划）

 

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

 

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

对于死锁，刚才说过了，只要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

 

通过察看sysobjects中ID，和index，我们可以找到对应的deadlock的table，通过分析执行计划，我们可以看出死锁发生的原因。具体内容，参考msdn文章，KBID是832524。

 

补充一下，GTEC也作SQL的case，虽然收费不菲，哈哈！

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

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

怎么捅破那层纸？（续）

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

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

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

有了这两样，我们就可以开始干活了！

 

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

l  XiongLi的blog，http://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最高的thread的call stack。如果内容都类似，说明我们抓到了导致hang的代码。下面的工作就是分析这些call stack了。

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

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看这段代码实现，我们知道了.NET的db conn是从pool里面得到的，而pool的大小是有限的。所以，我们猜测：程序中存在着db conn没有Close或者DataReader没有Close的问题。searchi一番，果然如此。修改后，问题解决。

 

对于Access Violation问题（简称AV，J），我们一般也使用adplus进行抓取。命令如下：

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

 

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

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

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 state、recycled等，也彻底麻木了。“很简单，重新启动机器就好了嘛！”。

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

 

（下面接着来做SQL Server的Performance Tuning，待续）