SQL Server性能调优入门（图文版）

swsrl

　　摘要：本文是转载，地址：http://blog.joycode.com/juqiang/archive/2007/01/19/91848.aspx
　　第一步，在业务高峰期抓取样本数据（2个小时左右）。采用的工具是sqlserver自带的profiler，也叫事件探查器，如下图：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image002.jpg
　　进入后，点击最左面的按钮，建立一个新的跟踪：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image004.jpg
　　登录需要用DBO权限，所以可以用sa登录，也可以用windows集成验证方式（如果当前登录的就是sqlserver的话）
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image006.jpg
　　新建跟踪，一共有4个tab页进行配置，首先看第一个。跟踪名称不用更改，默认的即可。保存一共有两种方式，一是文件，扩展名是.trc（这种方式方便你把客户那里的跟踪结果发给你），其二是数据库中的表。
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image008.jpg
　　为了分析方便，我们把它另存为表。此时sql提示你重新进行登录，这里我们把表保存到master中
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image010.jpg
　　假设表名字叫做jq（如果有重复的，系统会提示是否覆盖）
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image012.jpg
　　确定后回到了刚才的第一个tab页中：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image014.jpg
　　然后切换到第二个选项卡中：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image016.jpg
　　左面列出了各种事件类（Event Class），右面是当前已有的事件类。对于性能调优，我们不需要安全审核、会话信息，点击删除按钮即可：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image018.jpg
　　继续切换到第三个tab页上，这里的数据列默认就够了，当然，如果你看着不顺眼，可以把Appname/NT username等都删除。
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image020.jpg
　　最后一个tab页上，我们需要把系统自己产生的事件ID屏蔽掉：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image022.jpg
　　把那个排除系统ID进行check即可，如下图：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image024.jpg
　　所有项目配置好后，点击“运行”按钮。持续运行两个小时左右即可（业务高峰期，能典型的反应客户最近一段时间内的业务模式）
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image026.jpg
　　好了，第一步的准备工作完成了，等待一段时间后，我们开始检查刚才自动保存到master中的表jq。
　　第二步，开始查找影响速度的地方。
　　打开查询分析器（sql analyzer），登录到master中，从 表jq里面按照I/O倒序，读取若干个sql。根据我的习惯，一般是读取1000条记录。为什么根据I/O来找呢，而不是根据时间来找呢？原因很简单，一句SQL执行，“稳定”的是I/O，而duration是一个不稳定的因素。我们进行sql调优的目的，就是降低I/O成本，从而提高效率。（一般而言，I/O降低了，duration自然就会降低）详细内容，参考我以前的post：http://blog.joycode.com/juqiang
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image028.jpg
　　执行完成后，我们仔细看下面的输出。
　　1、 XL_TALLY_Proc04这个sp的reads最大，将近100w，duration也达到了25秒多。
　　2、 Erp_IM_GMBill_GetBill这个sp的I/O不算大，才7w，duration平均都在1秒多点。但是这个sp执行的次数非常多。
　　经过询问客户，XL_TALLY_Proc04这个sp执行的频度很低，一天也就一两次，但是Erp_IM_GMBill_GetBill大概5分钟就要一次。这样整体I/O就占用的非常大。
　　所以这里我们要重点分析Erp_IM_GMBill_GetBill这个sp，而不是第一个！
　　总结一个原则就是：调整的重点是客户最关心的内容，是执行频度最高、看起来I/O又比较大的那种。I/O最大的，不一定是我们要优先解决的内容。
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image030.jpg
　　第三步，开始分析刚才看到的那个语句。既然我们要分析I/O，那么就要把I/O打开，这样每次调整sql，我们都能随时看到I/O的变化情况。这句很有用处地：set statistics io on
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image032.jpg
　　单纯看I/O变化，我们会晕倒的。因为我们不知道自己做的任何改动，对I/O是如何产生影响的。所以，还要看sql的执行计划是怎佯的。 在查询分析器中，我们按Ctrl+K，或者如下图的菜单，check上即可。
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image034.jpg
　　好了，准备工作都做好了，下面开始干活了。
　　我们首先看sql语句的调优，假设下面这条sql语句性能低下：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image036.jpg
　　上面的sql一共读取了6636条数据，逻辑读是1126。那么这个I/O是否合理呢？大了还是小了？还有改进的余地吗？我们看执行计划：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image038.jpg
　　哦，一共4个咚咚在里面。Index seek的成本占了2%， index scan的占了47%，hash match占了51%，select最终是0%。我们应该牢记第二个原则，所有的index，尽可能的都走index seek。
　　我们看一下billsoflading的索引信息：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image040.jpg
　　当前索引为什么走scan，这里就不说了，感兴趣的可以随便找一本介绍数据库索引的书籍来看看即可。根据我以前那篇blog的描述，我们知道应该建立一个复合索引（也叫convered index）：boldate+companyid+bolcode
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image042.jpg
　　然后我们重新执行sql，看看I/O变化情况：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image044.jpg
　　Ooh，非常cool！logical reads降低到了50。为什么会这样呢？我们看一下执行计划：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image046.jpg
　　原来是index scan变成了index seek，效率自然大大的提升！
　　Sql语句在index上调优的方法，基本就是这样。我们继续看sp的。
　　
　　对于sp的调优，有一点是和sql调优不同的：sp内部的逻辑处理可能非常复杂。单纯从查询分析器中，我们无法得知哪一小块的sql执行的I/O最大，我们只能看到一个总体的描述。所以，我们要知道sp内部的信息。
　　首先，了解自己当前的spid是多少。一种方法是select @@spid，另一种方法是看查询分析器下面的status bar的信息。
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image048.jpg
　　Ooh，我的spid是101。（上图的最下面那个tips）
　　然后我重新打开profiler（事件探查器），重新建立一个跟踪，这里面要修改第二个tab页的信息，把左面事件列“存储过程”中的SmtpCompleted加上
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image050.jpg
　　增加后的样子如下：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image052.jpg
　　然后修改第4个tab页，把刚才看到的spid=101的信息填上：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image054.jpg
　　点击运行后，这样profiler只能抓到在查询分析器中，spid=101那个窗口发送的sql。我们切换回查询分析器，执行有问题的sp，执行完成后，我们再回到profiler，点停止按钮。一个sp内部所有执行的sql，都被分开了！
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image056.jpg
　　这次的结果假设保存在了jq2表中，我们把所有执行的小片sql都列出来：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image058.jpg
　　第一个是sp执行后的总体结果，I/O为62328，就是这个sp自己的。第二个是向临时表中插入数据，I/O为61514，我们很容易看到，这一句占用了整个sp的大概95%以上的成本。如果我们把这句insert into #temptable搞定，整个sp的成本自然就下来了。所以我们需要把这句insert搞出来。
　　但是慢着！default情况下，sqlserver的results只显示很少的字符，第二行的sql，我们根本抓不全的，所以我们需要修改一下设置。在查询分析器的工具-选项菜单中，切换到“结果”这个tab页，修改每列最多字符个数为8192（这是最大的允许值），然后点击“确定”按钮，重新从jq2中读取信息。也许你会问，如果某个sql特别长，怎么办？其实很简单，在你的代码中把这句sql单独写到log中，或者直接修改sp，把这句print出来即可。
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image060.jpg
　　Ok，我们把这句insert sql抓下来后，放到查询分析器中。因为temptable我们没有它的结构，所以我们把insert部分注释掉，看后面的select语句。执行后，ooh，在goodsmovement表上的成本是57834。
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image062.jpg
　　老办法，我们继续看执行计划：
http://blog.joycode.com/images/blog_joycode_com/juqiang/1368/o_WindowsLiveWriter_SQLServer_B707_clip_image064.jpg
　　其实，现在又回归到了sql调优的步骤，下面的工作我就不写啦！
　　
　　这个步骤，看起来很简单，希望大家对于sql调优（索引部分）心中都有这么一个概念，知道第一步作什么，第二步作什么。还是那句话，索引调优，基本上是最简单的。但是貌似简单的东西，我们越应该重视。你随便找一个应用跟踪一下，各种效率低下的索引，会让你实在#￥*#（**……￥