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产品）