最有效地优化 Microsoft SQL Server 的性能

规范逻辑数据库的设计

• 由于表较窄，会加快排序和创建索引的速度。
• 由于表较多，允许有更多的群集索引。
• 索引变得更窄、更紧凑。
• 每个表中的索引减少，有助于改善 UPDATE 操作的性能。
• 空数据和冗余数据减少，使数据库更为紧凑。
• 减少了 DBCC 诊断的并发影响，这是因为必要的表锁只会影响较少的数据。

使用有效的索引设计

• 检查 SQL 查询的 WHERE 子句，因为这是优化程序的重点。 
  
  WHERE 子句中列出的每一列都可考虑使用索引。如果检查的查询太多，不妨选择一个有代表性的集合，或只检查那些速度较慢的查询。如果您的开发工具以透明方式生成 SQL 代码，检查将更加困难。很多这样的工具出于调试目的，都允许将生成的 SQL 语法记入文件或屏幕。可能需要从工具供应商那里了解这一功能是否可用。
• 使用窄索引。 
  
  窄索引往往比多列的组合索引更为有效。窄索引在每页上有更多的行，同时具有较少的索引级别，因而能提升性能。 
  
  优化程序能快速而有效地分析几百个、甚至几千个索引和联接可能性。拥有更多的窄索引，将给优化程序提供更多的选择余地，这通常有助于提高性能。相反，拥有较少的多列宽索引，提供给优化程序的选择余地很少，这可能会损害性能。 
  
  通常最好不要采用一个强调完全覆盖查询的策略。如果 SELECT 子句中的所有列都被一个非群集索引覆盖，优化程序会识别出这一点，并可以提供很好的性能。不过，这通常会导致索引过宽，并会过度依赖于优化程序使用该策略的可能性。通常，您应使用数量更多的窄索引，这对于大量的查询来说可以提供更好的性能。 
  
  要获得足够的读取性能，您不应该有更多的索引，因为更新这些索引需要相当的开销。然而，即便最面向更新的操作，所需要的读操作也比写操作多得多。因此，如果您觉得使用新的索引会有帮助，那就不要犹豫；之后可以随时再将其删除。
• 使用群集索引。 
  
  适当使用群集索引可以极大地改善性能。甚至“更新”和“删除”操作速度也会因使用群集索引而大大加快，因为这些操作也需要很多的读取。每个表只允许有一个群集索引，因此使用时务必谨慎。返回很多行的查询或涉及一个数值范围的查询，都适合用群集索引来加速执行。 
  
  示例：
  

        SELECT * FROM PHONEBOOK
        WHERE LASTNAME=‘SMITH‘
  
        -or-
  
        SELECT * FROM MEMBERTABLE
        WHERE  MEMBER_NO > 5000
         AND MEMBER_NO < 6000
  
  						

  相反，如果这类查询很普通，则上述的 LASTNAME 或 MEMBER_NO 列就不太适合用非群集索引。非群集索引应尽量用在返回行数不多的列上。
• 检查列的唯一性。 
  
  这有助于您决定什么样的列适合使用群集索引、非群集索引，或不用索引。 
  
  下面是一个检查列唯一性的示例查询：
  

        SELECT COUNT (DISTINCT COLNAME)
        FROM TABLENAME
  
  						

  它会返回列中唯一值的数量。将这一数量与表中的总行数进行比较。对于一个 10,000 行的表，如果一列有 5,000 个唯一值，它就很适合使用非群集索引。在同一表中，如果一列有 20 个唯一值，它适合使用群集索引。而如果只有 3 个唯一值，就不要用索引。这些仅是例子而已，并不是死的、硬性的规定。请记住，索引应放在查询 WHERE 子句列出的各列上。
• 检查索引列的数据分布。 
  
  如果对某个只有很少唯一值的列创建了索引，或在这样一列上执行了联接，往往会导致查询速度缓慢。这对数据和查询来说是一个重大问题，如不了解情况就无法得到解决。例如，如果某城市所有的人都叫 Smith 或 Jones，那么想从一本姓氏按字母顺序排列的该市的电话簿中快速找到某个人是不太可能的。除了上面那个查询可以给出列唯一性的单个指标外，您还可使用 GROUP BY 查询来查看索引键值的数据分布。它提供了一个分辨率更高的数据视图，并为优化程序查看数据提供了更好的视角。 
  
  下面是一个检查索引键值数据分布的示例查询（假定 COL1、COL2 两列是关键字）：
  

        SELECT COL1, COL2, COUNT(*)
        FROM TABLENAME
        GROUP BY COL1, COL2
  
  						

  对应每一个键值都将返回一行，并带有各个值的实例计数。要减少返回的行数，可用 HAVING 子句排除一部分返回结果。例如，子句
  

        HAVING COUNT(*) > 1
  
  						

  将排除所有具有唯一关键字的行。 
  
  查询返回的行数也是关系索引选择的一个重要因素。优化程序认为一个非群集索引对返回的每一行至少要消耗一页 I/O。按这个速度计算，扫描整个表将变得更为有效。这也是限制结果集大小，或用群集索引查找大型结果的另一个原因。

使用有效的查询设计

• 大型结果集
• IN、NOT IN 和 OR 查询
• 高度非唯一 WHERE 子句
• !=（不等于）比较运算符
• 某些列函数，如 SUM
• WHERE 子句中的表达式或数据转换
• WHERE 子句中的局部变量
• GROUP BY 的复杂视图

   SELECT SUM(SALARY) FROM TABLE
				

   SELECT SUM(SALARY) FROM TABLE WHERE
   ZIP=‘98052‘
				

   SELECT * FROM TABLE WHERE
   LNAME=@VAR
				

   SELECT * FROM TABLE
   WHERE LNAME=@VAR AND ZIP=‘98052‘
				

使用有效的应用程序设计

• 使用小型结果集。为客户端浏览检索不必要的大型结果集（例如，好几千行），将会增加 CPU 和网络 I/O 负担，使应用程序不能十分有效的用于远程，同时还限制了多用户扩展性。因此您设计的应用程序最好提示用户输入足够的信息，以便提交的查询可以生成最适当的结果集。 
  
  有助于达到这一目的的应用程序设计技术包括：建立查询时限制通配符的使用、强制要求输入某些字段、禁止随意性的查询。
• 在 DB-Library 应用程序中正确使用 dbcancel()。所有的应用程序都应当允许取消正在进行的查询。任何应用程序都不能迫使用户通过重新启动计算机来取消查询。不遵循这个原则就会出现无法解决的性能问题。在使用 dbcancel() 时，对事务级应给予一定的关注。有关其他信息，请参见以下 Microsoft 知识库文章：
  
  117143：INF：使用 dbcancel() 或 sqlcancel() 的条件或方法
  如果使用了 ODBC sqlcancel() 调用，ODBC 应用程序也会出现同样的问题。
• 一定要处理完所有结果。不要设计没有取消查询就停止结果行处理的应用程序，或使用这类转包应用程序。这样做通常会导致阻塞并降低性能。
• 一定要实现查询超时。不能让查询无限期地运行。请用适当的 DB-Library 或 ODBC 调用设置查询超时。在 DB-Library 中，查询超时是通过 dbsettime() 调用实现的，在 ODBC 中是通过 SQLSetStmtOption() 实现的。
• 不要使用不允许对发送给服务器的 SQL 语句进行显式控制的应用程序开发工具。不要使用基于更高级对象以透明方式生成 SQL 语句的工具，除非它提供了查询取消、查询超时、完全事务控制等重要功能。如果由应用程序本身生成所有的“透明 SQL”，要保持好的性能或解决性能问题通常不太可能，因为这将不允许显式控制事务性和锁定问题，而它们对性能是很关键的。
• 不要把决策支持与联机事务处理 (OLTP) 查询混在一起。
• 不要设计强迫用户通过重新启动客户机来取消某个查询的应用程序，或使用这样的转包程序。由于可能有处于无主状态的连接，这将导致许多难以解决的性能问题。有关更多信息，请参阅下面的 Microsoft 知识库文章：
  
  137983 INF：如何排除 SQL Server 中无主连接的问题

分析性能缓慢问题的技术

• 根据 SQL Server 4.2x“故障排除指南”和 SQL Server 6.0“Transact-SQL 参考”中的说明使用 4032 跟踪标记。这将在 SQL 错误日志中捕获发送到服务器的 SQL 语句。
• 通过使用像“Microsoft 网络监视器”（它是 Systems Management Server 的一部分）这样的网络分析程序来监视查询。
• 对于 ODBC 应用程序，使用 ODBC Administrator 程序来选择跟踪 ODBC 调用。有关详细信息，请参见 ODBC 文档。
• 使用在 DB-Library 或 ODBC 层截获 SQL 的第三方客户端工具。Blue Lagoon Software 的 SQL Inspector 便是一个例子。
• 使用 Microsoft TechNet CD 中作为示例提供的 SQLEye 分析工具。注意： SQLEye 不在 Microsoft 技术支持范围之内。

• 使用 ISQL 之类的查询工具，独立运行被怀疑速度很慢的查询，对其实际速度进行验证。最佳做法是用 ISQL 及本地管道在服务器计算机上运行查询，然后再将输出重定向到文件中。这有助于消除复杂的因素（如网络和屏幕 I/O）以及应用程序结果缓冲的影响。
• 使用 SET STATISTICS IO ON 来检查查询占用的 I/O。注意逻辑页 I/O 计数。优化程序的目标是使 I/O 计数最小。记录逻辑 I/O 计数。这将成为衡量性能改善程度的基准。与使用 SET SHOWPLAN ON 相比，专门关注 STATISTICS IO 输出，并试用不同的查询和索引类型，会更为有效。解释并有效地应用 SHOWPLAN 输出需要作一些研究工作，把它占用的时间花在一些试验性测试上可能效率更高。如果这些简单的建议还不能解决性能问题，您可以使用 SHOWPLAN 对优化程序行为作更彻底的调查。
• 如果查询涉及一个视图或存储过程，把它从视图或存储过程提取出来单独运行。在您尝试使用不同的索引时，这将允许对访问计划进行更改。这还有助于把该问题定位在查询本身，而不是优化程序如何处理视图或存储过程。如果查询本身没有问题，而在查询作为视图或存储过程的一部分运行时出现问题，那么独立地运行查询也将有助于确定这一点。
• 注意查询涉及的表上可能存在的触发器，它们在运行时会产生明显的 I/O。应删除慢速查询中涉及的触发器。这将有助于确定问题是与查询本身有关，还是与触发器或视图有关，从而确定您努力的方向。
• 检查慢速查询所使用的表的索引。通过上面列举的技术来确定这些索引是否合理，并对其进行必要的更改。您努力的第一步应是对 WHERE 子句中的每一列进行索引。通常，性能问题是由于 WHERE 子句中的列没有进行索引，或者这些列上没有有效的索引而引起的。
• 用上面提到的查询来检查 WHERE 子句中提到的每一列（尤其是每一索引列）的数据唯一性和分布。在很多情况下，只需对查询、表、索引和数据进行简单的检查，便能立即找出问题的起因。例如，出现性能问题通常是由于对一个只有 3 个或 4 个唯一值的关键字进行了索引，或在这样的列上执行了联接，或把过多的行数返回给客户端。
• 在这些研究的基础上，对应用程序、查询或索引进行必要的更改。完成更改后，再次运行查询，并观察 I/O 计数的变化。
• 如果发现速度有所提高，请运行主应用程序，看看整体性能有没有提高。

• 使用 Windows NT 性能监视器来监视 I/O 与 CPU 的活动。监视 LogicalDisk 对象“% Disk Time”计数器的所有实例。同时监视 System 对象的“% Total Processor Time”计数器。要查看有效的磁盘性能信息，您必须先从命令提示符发出“diskperf -Y”，打开 Windows NT DISKPERF 设置，然后重新启动系统。有关详细信息，请参见 Windows NT 文档。
• 运行查询时，如果 CPU 图形曲线一直很高（例如，高于 70 %），而“% Disk Time”值一直很低，说明这是一个 CPU 受限的状态。
• 运行查询时，如果 CPU 图形曲线一直很低（例如，低于 50 %），而“% Disk Time”一直很高，说明这是一个 I/O 受限的状态。
• 将 CPU 图形曲线与 STATISTICS IO 信息进行比较。

结论