SQL Server常见性能问题的优化

 

      SQL Server的常见性能问题，可大体从三个方面入手考虑，操作系统和数据库的配置，表/索引/存储的设置，SQL语句等。对于已交付的系统，往往由于实际情况的限制，SQL语句的优化比较困难。本文总结了SQL Server性能优化的实用方法。

 

与操作系统相关的优化

      操作系统性能的好坏直接影响数据库的使用性能，如果操作系统存在问题，如cpu过载、过度内存交换、磁盘i/o瓶颈等，在这种情况下，单纯进行数据库内部性能调整是不会改善系统性能的。我们可以通过windows nt的系统监视器(system monitor)来监控各种设备，发现性能瓶颈。

      cpu 一种常见的性能问题就是缺乏处理能力。系统的处理能力是由系统的cpu数量、类型和速度决定的。如果系统没有足够的cpu处理能力，它就不能足够快地处理事务以满足需要。我们可以使用system monitor确定cpu的使用率，如果以75%或更高的速率长时间运行，就可能碰到了cpu瓶颈问题，这时应该升级cpu。但是升级前必须监视系统的其他特性，如果是因为sql语句效率非常低，优化语句就有助于解决较低的cpu利用率。而当确定需要更强的处理能力，可以添加cpu或者用更快的cpu 替换。

      内存 sql server可使用的内存量是sql server性能最关键因素之一。而内存同i/o子系统的关系也是一个非常重要的因素。例如，在i/o操作频繁的系统中，sql server用来缓存数据的可用内存越多，必须执行的物理i/o也就越少。这是因为数据将从数据缓存中读取而不是从磁盘读取。同样，内存量的不足会引起明显的磁盘读写瓶颈，因为系统缓存能力不足会引起更多的物理磁盘i/o。

      可以利用system monitor检查sql server的buffer cache hit ratio计数器，如果命中率经常低于90%，就应该添加更多的内存。

      i/o子系统 由i/o子系统发生的瓶颈问题是数据库系统可能遇到的最常见的同硬件有关的问题。配置很差的i/o子系统引起性能问题的严重程度仅次于编写很差的sql语句。i/o子系统问题是这样产生的，一个磁盘驱动器能够执行的i/o操作是有限的，一般一个普通的磁盘驱动器每秒只能处理85次i/o操作，如果磁盘驱动器超载，到这些磁盘驱动器的i/o操作就要排队，sql的i/o延迟将很长。这可能会使锁持续的时间更长，或者使线程在等待资源的过程中保持空闲状态，其结果就是整个系统的性能受到影响。

      解决i/o子系统有关的问题也许是最容易的，多数情况下，增加磁盘驱动器就可以解决这个性能问题。

 

精确定位数据库性能瓶颈的根源

      性能问题往往与数据库的设计与SQL语句有关。用户作性能优化时，目标不是让我们对硬件设施提出升级的建议。我们需要找到性能的真正根源，而不是似是而非的借口。

      虽然性能问题各有不同，但采用一般性方法，能诊断分析大部分问题。如果您在某种程度上具有资源瓶颈，应尽量找出产生这一瓶颈的所有原因。例如，如果由于 CPU 使用过度而导致吞吐量明显降低（即具有 CPU 瓶颈），建议首先确定服务器上的五大 CPU 用户。如果由于 I/O 饱和而导致吞吐量明显降低（即具有 I/O 瓶颈），建议首先确定服务器上的五大 I/O 用户。

      是否出现了锁阻塞（锁竞争）？建议首先分析正在争用哪些资源（表和索引），并确定该争用所涉及的五大 SQL 语句。虽然未说明问题是出自 SQL Server 2000 还是 SQL Server 2005，但确定不同瓶颈产生原因的方法在各平台之间只是略有不同。这些一般性经验法则应该可以对您有所帮助。在任一平台上，您都可以将调查范围限制到特定的数据库中。

 

在 SQL SERVER 数据库中减少锁使用

      锁的使用是导致性能的一个重要因素，过多地锁可能导致性能的严重下降，甚至出现死锁的现象。同时，锁在任何具有多个用户的数据库应用程序中都是不可避免的。但从另一方面来说，如果应用程序出现大量锁，您可以采取相应措施。

      除了重新设计系统之外，首选的措施就是评估索引编制策略。如果您的应用环境中存在聚簇索引，请确保它们在 8K 大小页面上存储数据，并彼此远离。甚至于还需要确保"填充因子"（和Pad Index）均设置为非默认值，可能是 75% 到 80%，这样可以在数据页之间插入更多空间。作为辅助措施，请确保您的事务获得最少量的必需锁。同时尽量限制对通配符（例如 SELECT * FROM...）的使用，尤其是限制 INSERT、UPDATE 和 DELETE 事务保持打开和未提交状态的时间。

      其次，可通过使用 SET DEADLOCK_PRIORITY 命令设置一个事务或一组事务总是低优先级（或总是高优先级）。

      最后，您可变更给定连接的默认锁定行为，具体途径包括使用 SET TRANSACTION ISOLATION LEVEL 命令，或使用诸如 NOLOCK 的查询提示来修改单个查询的行为以按查询进行变更。如果您没有慎重考虑变更 SQL Server 数据库固有行为所带来的后果，建议您不要执行此操作。

 

合理使用索引

      索引是数据库中重要的数据结构，它的根本目的就是提高查询效率。索引的使用要恰到好处，其使用原则如下：

      在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引；在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引；在条件表达式中经常用到的不同值较多的列上建立索引，在不同值少的列上不要建立索引。比如在雇员表的"性别"列上只有"男"与"女"两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。 如果待排序的列有多个，可以在这些列上建立复合索引。

 

常见SQL语句技巧

      1.如果有独特的索引，那么带有"="操作符的WHERE子句性能最好，其次是封闭的区间(范围)，再其次是开放的区间。

      2.从数据库访问的角度看，含有不连续连接词(OR和IN)的WHERE子句一般来说性能不会太好。所以，优化器可能会采用R策略，这种策略会生成1个工作表，其中含有每个可能匹配的执行的标识符，优化器把这些行标志符(页号和行号)看做是指向1个表中匹配的行的"动态索引"。优化器只需扫描工作表，取出每一个行标志符，再从数据表中取得相应的行，所以R策略的代价是生成工作表。

      3.包含NOT、<>、或!　=的WHERE子句对于优化器的索引选择来说没有什么用处。因为这样的子句是排斥性的，而不是包括性的，所以在扫描整个原来数据表之前无法确定子句的选择性。

      4.限制数据转换和串操作，优化器一般不会根据WHERE子句中的表达式和数据转换式生成索引选择。例如:

      paycheck * 12>36000 or substring(lastname,1,1)="L"

      如果该表建立了针对paycheck和lastname的索引，就不能利用索引进行优化，可以改写上面的条件表达式为:

      paycheck<36000/12 or lastname like "L%"

      5.如果没有包含合并子句的索引，那么优化器构造1个工作表以存放合并中最小的表中的行。然后再在这个表上构造1个分簇索引以完成一个高效的合并。这种作法的代价是工作表的生成和随后的分族索引的生成，这个过程叫REFORMATTING。　　所以应该注意RAM中或磁盘上的数据库tempdb的大小(除了SELECT INTO语句)。另外，如果这些类型的操作是很常见的，那么把tempdb放在RAM中对于提高性能是很有好处的。