怎样查出SQLServer的性能瓶颈

怎样查出SQLServer的性能瓶颈--王成辉翻译整理，转贴请注明出自微软BI开拓者[url][/url]--原帖地址如果你曾经做了很长时间的DBA，那么你会了解到SQLServe的性能调优不是一个精密的科学。即使是，对于为最佳的性能找到最佳的配置也是很困难的。这是因为对于调优来说很少东西是绝对的。例如，一个性能调优可能对某一方面有用，可是却会影响其他的性能。我曾经做过DBA，在最后7年的日子里，我总结了一套SQLServer调优的清单。当第一次进行SQLServer性能调优的时候，可以用它来作为一个向导。我经常被邀请去检查SQLServer并提供一些性能方面的建议。直到现在，我还没有真正写下一个贯穿整个性能调优过程的方案。但是当我做了越来越多的性能调优的咨询工作后，我现在决定花点时间整理出来。你将会发现它是很有用的，就象我发现对我的用处一样.SQLServer性能监控这套性能优化的清单将至少准科学的帮助你找出你的SQLServer任何明显的性能问题。说是这样说，SQLServer的性能调优仍然是很困难的。我试图用这套清单去找出“容易”的sqlserver性能问题，困难的留待稍后。我这样做是因为很容易将容易和困难的的性能调优问题搞混。通过列出一个“容易”的性能调优范围，就很容易的将这些问题解决，一旦解决了这些容易的问题，那么你就能集中去解决更困难的问题。使用这个SQLServer性能调优清单的一个好处是，它将不仅仅告诉你目前最容易解决的性能问题是什么，而且还帮助你正确的去解决。在某种程度上，你可以选择不同的顺序进行。换句话说，你可以故意做出特殊的决定而不是按照清单通常的顺序进行。某种意义上说你是对的，不是所有的性能调优建议都适合所有的情形。另外，你的决定是基于你的资源限制，例如没有足够的钱去买满足负荷的硬件。如果真是那样的话，你就别无选择了。还有，你的决定可能基于一些政治原因，那是你不得不作出的改变。不管怎样，你需要知道你能做什么，使用这个性能调优清单找出你能改变的范围并做出相应的改变提升你的SQLServer的性能。一般来说，你将在你的每一个SQL服务器上执行这个清单。如果遇到清单中的一些问题，这会花掉你一些时间。我建议你从目前性能问题最多的的服务器开始，然后当你有时间的时候按照自己的思路去解决其他服务器。一旦你完成了，可仍然有很多事情要去做。记住，这些只是一些容易的。一旦你完成了这些容易的，接下来你需要花时间去解决更困难问题。这个是另一篇文章要解决的问题了。怎样进行你的SQLServer性能调优呢?为了使其变得容易，我把它们分成了以下几个部分：？使用性能监视器找出硬件瓶颈？SQLServer硬件性能监控列表？操作系统性能监控列表？SQLServer2000配置性能监控列表？数据库配置设置性能监控列表？索引性能监控列表？应用程序和T-SQL性能监控列表？SQLServer数据库作业性能监控列表？使用Profiler找出低效的查询？怎样最好的实现SQLServer性能监控管理你的SQLServe性能的最好方法是首先回顾上面每一部分的内容，把它们打印出来。然后完成每一部分的内容，写下你收集到的结果。你也可以按照你喜欢的顺序进行。上面的步骤仅仅列出了我执行的顺序，因为那样通常能达到一个比较好的效果。性能监控列表计数器名称均值最小值最大值Memory:Pages/secMemory:AvailableBytesPhysicalDisk:%DisktimePhysicalDisk:Avg.DiskQueueLengthProcessor:%ProcessorTimeSystem:ProcessorQueueLengthSQLServerBuffer:BufferCacheHitRatioSQLServerGeneral:UserConnections在上表输入你的结果.使用性能监视器找出SQLServer硬件瓶颈开始SQLServer性能调优的最佳地方就是从性能监视器（系统监视器）开始。通过一个24小时的周期对一些关键的计数器进行监控，你将对你SQLServer服务器的硬件瓶颈了如指掌。一般来说，使用性能监视器去创建一个一些关键的计数器的24小时周期的监控日志。当你决定创建这个日志的时候，你需要选择一个典型的24小时的周期，例如，选择一个典型的比较忙的日期，而不是周日或节假日。一旦你将这些捕获的数据形成日志后，在性能监视器的图形界面下会显示计数器的推荐值。你在上表中记下均值、最小值、峰值。做完这些后，用你的结果跟下面的分析比较。通过你的结果和下面的建议值进行比较，你将能快速的找到你的SQLServe正在经历的潜在的硬件瓶颈。关键性能计数器说明下面是不同关键性能计数器的一个讨论，它们的建议值和为了帮助解决硬件瓶颈问题的一些选项。注意我已经限制了性能监视器需要监视的一些关键计数器。我这么做是因为在本文我们的目的是为了容易的找到显而易见的性能问题，许多其他的性能监视器计数器你能在本网站其他地方找到。Memory:Pages/sec这个计数器记录的是每秒钟内存和磁盘之间交换的页面数。交换更多的页面、超过你服务器承受的更多的I/O，将轮流降低你SQLserver的性能。你的目的就是尽量将页面减少到最小，而不是消除它。如果你的服务器上SQLServer是最主要的应用程序，那么这个值的理想范围是0～20之间。可能很多时候你看到的值都会超过20。这个值一般要保持在每秒的平均页数在20以下。如果这个值平均总是超过20，其中最大的一个可能是内存瓶颈问题，需要增加内存。通常来说，更多的内存意味着需要执行的页面更少。在大多数情况下，服务器决定SQLServer使用的适当内存的大小，页面将平均小于20。给SQLServer适当的内存意味着服务器的缓存命中率（BufferHitCacheRatio这个稍后会讲到）达到99％或者更高。如果在一个24小时的周期里你的sqlserver的缓存命中率达到99％或者更高，但是在这个期间你的页面数总是超过20，这意味着你或许运行了其他的程序。如果是这样的情况，建议你移除这些程序，使SQLServer是你的服务器的最主要的程序。如果你的sqlserver服务器没有运行其他程序，并且在一个24小时的周期里页面数总是超过20，这说明你应该修改你对SQLServer的内存设置了。将其设置为“动态配置SQLServer的内存”，并且最大内存设置得高一些。为了达到最优，SQLServer将尽可能的获得多的内存以完成自己的工作，而不是去和其他的程序争夺内存。Memory:AvailableBytes另一个检查SQLServer是否有足够的物理内存的方法是检查MemoryObject:AvailableBytes计数器。这个值至少大于5M，否则需要添加更多的物理内存。在一个专门的SQLServer服务器上，SQLServer试图维持4-10M的自由物理内存，其余的物理内存被操作系统和SQLServer使用。当可用的物理内存接近5M或者更低时，SQLServer最可能因为缺少内存而遇到性能瓶颈。遇此情况，你需要增加物理内存以减少服务器的负荷，或者给SQLServer配置一个合适的内存。PhysicalDisk:%DiskTime这个计数器度量磁盘阵列繁忙程度（不是逻辑分区或磁盘阵列上独立的磁盘）。它提供一个对磁盘阵列繁忙程度相对较好的度量。原则上计数器%DiskTime的值应该小于55%。如果持续超过55%（在你24小时的监控周期里大约超过10分钟），说明你的SQLServer有I/O瓶颈。如果你只是偶尔看到，也不必太担心。但是，如果经常发生的话（也就是说，一个小时出现好几次），就应该着手寻找增加服务器I/O性能或者减少服务器负荷的解决之道了。一般是为磁盘阵列增加磁盘，或者更好更快的磁盘，或者给控制器卡增加缓存，或者使用不同版本的RAID，或者更换更快的控制器。在NT4.0上使用该计数器之前，确认在NT命令提示符下输入diskperf-y，重启服务器，以便手动打开。在NT4.0下第一次必须将该计数器打开，Windows2000默认是打开的。PhysicalDisk:Avg.DiskQueueLength除了观察物理磁盘的%DiskTime计数器外，还可以用Avg.DiskQueueLength计数器。磁盘阵列中的各个磁盘的该值如果超过2（在你24小时的监控周期里大约超过10分钟），那么你的磁盘阵列存在I/O瓶颈问题。象计数器%DiskTime一样，如果只是偶尔看到，也不必太担心。但是，如果经常发生的话，就应该着手寻找增加服务器I/O性能的解决之道了。如前所述。你需要计算这个值，因为性能监视器不知道你的磁盘阵列中有多少物理磁盘。例如，如果你有一个6个物理磁盘组成的磁盘阵列，它的Avg.DiskQueueLength值为10，那么实际每个磁盘的值为1.66（10/6＝1.66），它们都在建议值2以内。在NT4.0上使用该计数器之前，确认在NT命令提示符下输入diskperf-y，重启服务器，以便手动打开。在NT4.0下第一次必须将该计数器打开，Windows2000默认是打开的。一起使用这两个计数器将帮助你找出I/O瓶颈。例如，如果%DiskTime的值超过55%，Avg.DiskQueueLength计数器值超过2，服务器则存在I/O瓶颈。Processor:%ProcessorTime处理器对象：%ProcessorTime计数器对每一个CPU可用，并针对每一个CPU进行检测。同样对于所有的CPU也可用。这是一个观察CPU利用率的关键计数器。如果%TotalProcessorTime计数器的值持续超过80%（在你24小时的监控周期里大约超过10分钟），说明CPU存在瓶颈问题。如果只是偶尔发生，并且你认为对你的服务器影响不大，那没问题。如果经常发生，你应该减少服务器的负载，更换更高频率的CPU，或者增加CPU的数量或者增加CPU的2级缓存（L2cache）。System:ProcessorQueueLength根据%ProcessorTime计数器，你可以监控ProcessorQueueLength计数器。每个CPU的该值如果持续超过2（在你24小时的监控周期里大约超过10分钟），那么你的CPU存在瓶颈问题。例如，如果你的服务器有4个CPU，ProcessorQueueLength计数器的值总共不应超过8。如果ProcessorQueueLength计数器的值有规律的超过建议的最大值，但是CPU利用率相对不是很高，那么考虑减少SQLServer的maxworkerthreads的配置值。ProcessorQueueLength计数器的值高的可能原因是有太多的工作线程等待处理。通过减少maximumworkerthreads的值，强迫线程池踢掉某些线程，从而使线程池得到最大的利用。一起使用计数器ProcessorQueueLength和计数器%TotalProcessTime，你可以找到CPU瓶颈，如果都显示超过它们的建议值，可以确信存在CPU瓶颈问题。SQLServerBuffer:BufferCacheHitRatioSQLServerBuffer中的计数器BufferCacheHitRatio用来指出SQLServer从缓存中而不是磁盘中获得数据的频率。在一个OLTP程序中，该比率应该超过90%，理想值是超过99%。如果你的buffercachehitratio低于90%，你需要立即增加内存。如果该比率在90%和99%之间，你应该认真考虑购买更多的内存了。如果接近99%，你的SQLServer性能是比较快的了。某些情况下，如果你的数据库非常大，你不可能达到99%，即使你在服务器上配置了最大的内存。你所能做的就是尽可能的添加内存。在OLAP程序中，由于其本身的工作原理，该比率大大减少。不管怎样，更多的内存总是能提高SQLServer的性能。SQLServerGeneral:UserConnecti