SQL Server 性能优化工具(4)

“Disk Write Bytes/sec”和“Disk Read Bytes/sec”计数器用每个逻辑驱动器的每秒字节数表示数据吞吐量。将这些数字与“Disk Reads/sec”和“Disk Writes/sec”一起仔细考虑。不要因为每秒的字节数较低就以为磁盘 I/O 子系统不忙！请记住一个硬盘每秒钟可以支持 75 个非连续和 150 个连续的磁盘读和磁盘写。
监视与 SQL Server 文件相关的所有驱动器的“Disk Queue Length”，并确定哪些文件与过长的磁盘队列相关。
如 果 Performance Monitor 显示某些驱动器没有另一些驱动器繁忙，便有机会将 SQL Server 文件从出现瓶颈的驱动器中移到不忙的驱动器中。这有助于将磁盘 I/O 活动更均匀地分布在硬盘中。如果某个大型的驱动器池正在为 SQL Server 文件使用，那么磁盘队列的解决方案是通过在这个驱动器池中添加更多的物理驱动器来加大池的 I/O 容量。
磁盘队列可能是某个 SCSI 通道已被 I/O 请求饱和的征兆。Performance Monitor 不能直接检测是否是这种情况。硬件厂商可能可以提供某些工具来帮助检测某个 RAID 控制器所服务的 I/O 数以及该控制器是否对 I/O 请求进行排队。如果许多磁盘驱动器（10 个或更多）连到了此 SCSI 通道，且它们均以全速执行 I/O，那么这种情况更有可能发生。这种情况的解决方案是取出一半磁盘驱动器，然后将它们连到另一个 SCSI 通道或 RAID 控制器以平衡这些 I/O。通常，在 SCSI 通道中重新平衡驱动器要求重建 RAID 阵列并完全备份/恢复 SQL Server 数据库文件。
Performance Monitor 图形输出示例

图 8 表示可用 Performance Monitor 进行观察的典型计数器。请注意当前所观察的计数器是 Processor Queue Length。按用亮白色突出显示当前的计数器。这有助于将当前计数器与其它正在观察的计数器区分开来，当用 Performance Monitor 同时观察许多计数器时，这种方法尤其有用。
请 注意 Processor Queue Length 的 Max（最大）值是 22.000。Performance Monitor 图形的 Max、Min 和 Average 值仅涵盖 Graph Time（图形时间）所示图形的当前时间窗口。默认情况下，Graph Time 可涵盖 100 秒。要监视更长时间，并确保获得这些时间段中有代表性的 Max、Min 和 Average 值，可使用 Performance Monitor 的记录功能。
Processor Queue（处理器队列）图形线条的形状表示 Max 值 22 仅持续了一段很短的时间。但是在值 22 的前面有一段时间 Processor Queue Length 大于 5（这可从图上看出，图中 100% 表示 22，在值 22 的前面有一段时间图形的值超过 25%，即大约为 5。）在本例中，数据库服务器 \\HENRYLNT2 只有一个处理器，不能承受 Processor Queue Length 大于 2。因此，Performance Monitor 指出这台机器上的处理器负荷有时过重，需进一步调查以减少处理器上的负荷，或者在 \\HENRYLNT2 上添加更多的处理器以处理这些时间段中的较高的处理器工作负荷。

图 8 Performance Monitor 图形输出
其它性能主题

减少网络流量和数据库服务器的资源消耗

通过易于使用的接口（如 ADO/RDO/DAO 数据库 API）完成 SQL 作业的数据库程序员，依然有责任密切关注所生成的结果集。ADO/RDO/DAO 为程序员提供优秀的数据库开发接口，这些接口具有丰富的 SQL 行集功能，且不要求程序员具有丰富的 SQL 编程经验。但这同时也意味着需要付出一定的代价。如果由于程序员没有仔细考虑其应用程序返回到客户端的数据量，不注意 SQL Server 索引放置的位置以及 SQL Server 数据的排列方法，就会引起性能问题。SQL Profiler、Index Tuning Wizard 和 ShowPlan 对于找到和修复这些有问题的查询十分有帮助。
通过消除选择列表中不需要返回的列或者仅返回所需要的行来减小返回的结果集。这有助于减少 I/O 和 CPU 的消耗。
有 关详细信息，请在 SQL Server Books Online 中搜索字符串“Optimizing Application Performance Using Efficient Data Retrieval”、“Understanding and Avoiding Blocking”和“Application Design”。
死锁

如果在构造访问 SQL Server 的应用程序时，使事务按相同的时间顺序访问所有用户事务中的表，则可以避免死锁。在应用程序设计过程中，有必要尽早向 SQL 应用程序开发人员明确阐明按时序访问表的概念。这有助于避免死锁问题，以免将来需要付出更高的代价解决这个问题。
减少 SQL 查询 I/O 并缩短事务时间：虽说这是一种防止死锁的迂回办法，但对所有的查询都应使用。它可能会有一些帮助，因为它可以加快查询的速度，从而减少将资源锁定的时间以 及所有锁定的竞争（包括死锁）。使用 SQL Query Analyzer 的 SHOW STATS I/O 来确定与大型查询有关的逻辑页提取的数目。考虑选择 SQL Query Analyzer 的“Show query plan”选项时所使用的索引。考虑索引的放置或者重新设计 SQL 查询以使它更有效，从而使用更少的 I/O。
有关详细信息，请在 SQL Server Books Online 中搜索字符串“Avoiding Deadlocks”、“Troubleshooting Deadlocking”、“Detecting and Ending Deadlocks”和“Analogy to Nonserializable Transactions”。

只要有任何可能就应避免的 SQL

在 SQL 查询中使用不等号可以迫使数据库使用表扫描来求取不等式的值。如果在超大型表中定期运行这些查询，会产生较高的 I/O。
示例：
WHERE!= some_value
WHERE<> some_value
其中带有 NOT 的任何 WHERE 表达式
如果需要运行这些查询，试着重新构建查询以删除 NOT 关键字。
示例：
不用：
select * from tableA where col1 != "value"
试着使用；
select * from tableA where col1 < "value" or col1 > "value"
如果索引创建在 col1，这使 SQL Server 得以使用这种索引（在这种情况下用聚集索引更好），而不需要求助于表扫描。
灵巧化标准

在极热（存取频繁）的表中，如果有几列是 SQL 应用程序不经常需要的，则将它们移到另一个表是比较有意义的。删除的列越多，就越有利于减少 I/O 并提高性能。有关详细信息，请在 SQL Server Books Online 中搜索字符串“Logical Database Design”和“Normalization”。
分区视图

SQL Server 7.0 可以通过视图对表进行水平分区。当数据库用户希望维持引用某个表名称的 SQL 查询，但数据检索的本性总是查询数据的固定分段时，使用分区可以改善 I/O 性能。例如，假定有一个记录所有销售部门一年销售情况的超大型表，并假定这个表中的所有检索都基于一个销售部门。在这种情况下，可以使用分区视图。可以为 每个销售部门定义一个销售表，在每个表的销售部门列中定义一个约束，然后在所有的表中创建一个视图，以形成分区视图。销售部门列中的约束由 Query optimizer 使用。当查询视图时，与查询中所提供销售部门值不匹配的所有销售部门表都将被 Query optimizer 忽略，而不对这些基表执行 I/O。通过减少 I/O 改善了查询性能。
复制和备份性能

确保磁盘 I/O 子系统和 CPU 运行很好，可以改善所有 SQL Server 操作的性能。其中当然包括复制和备份。事务复制和事务日志备份涉及到读取事务日志文件。快照复制和备份执行数据库文件的连续扫描。SQL Server 7.0 的新型存储结构已经过改进，使这些操作既快速又有效（只要数据库服务器的 CPU 或磁盘子系统中没有发生排队现象）。
有关性能优化 复制和备份/恢复的详细信息，请在 SQL Server Books Online 中搜索字符串“Replication Performance”、“Optimizing Backup and Restore Performance”、“Creating and Restoring Differential Database Backups”、“Creating and Applying Transaction Log Backups”、“Using Multiple Media or Devices”、“Minimizing Backup and Recovery Times in Mission-Critical Environments”、“Backup/Restore Architecture”和“SQL Server 7.0 on Large Servers”。
特殊的磁盘 I/O 调整方案：EMC 对称集成高速缓存磁盘队列

对于在 EMC Symmetrix Enterprise Storage Systems（EMC 对称企业存储系统）中执行的 SQL Server 数据库系统，应记住几种磁盘 I/O 平衡方法，因为 EMC 对称存储的独特特性有助于避免磁盘 I/O 瓶颈问题并获得最佳性能。
对 称存储系统包含高达 16 GB 的 RAM 高速缓存，并在磁盘阵列中包含内部处理器，这有助于加速数据的 I/O 处理，而无需使用托管服务器的 CPU 资源。要了解怎样平衡磁盘 I/O，请关注 Symmetrix（对称）框内的 4 个主要组件。16 GB 高速缓存是其中之一。最大可用 32 个 SA 通道将 32 个 SCSI 卡从 Windows NT 托管服务器连接到 Symmetrix，所有这些通道可以同时请求 16 GB 高速缓存中的数据。而 Symmetrix 框中最大有 32 个称为 DA 控制器的连接器，它们是内部 SCSI 控制器，用来将 Symmetrix 中的所有内部磁盘驱动器连到内部高速缓存中。这样，在 Symmetrix 中就形成了硬盘。
关于 EMC 硬盘的备注：它们是 SCSI 硬盘，与本文中所讨论的其它 SCSI 驱动器具有相同的 I/O 能力（这里适用 75/150 规则）。EMC 技术通常使用的一个功能是“超级卷”（hyper-volumes）。超级卷定义为 EMC 硬盘的逻辑分区，对于 Windows NT 磁盘管理器来说，超级卷就像另一个物理驱动器，因此用 Windows NT 磁盘管理器可以像对其它任何磁盘驱动器一样对它们进行操作。可以在每个物理驱动器上定义多个超级卷。当在 EMC 存储中执行数据库性能优化时，很重要的一点是一定要和 EMC 域工程师密切合作，以了解超级卷是如何定义的（如果有的话），其原因在于避免物理驱动器的数据库 I/O 超载很重要。如果以为两个或多个超级卷是单独的物理驱动器，而实际上它们是同一个物理驱动器上的两个或多个超级卷，那么就极有可能发生超载。
应 该在不同的 DA 控制器中平均分配 SQL Server I/O 活动。这是因为 DA 控制器是分配给所定义的硬盘组的。本文前面已讨论过，SCSI 控制器不可能发生瓶颈。DA 控制器不大可能发生 I/O 瓶颈，但是与 DA 控制器相关的驱动器组较有可能出现瓶颈。在 DA 控制器及其相关磁盘驱动器环境中，SQL Server 磁盘 I/O 平衡的方法与其它任何厂商的磁盘驱动器和控制器的方法相同。
如果要监视 DA 通道或单独的物理硬盘上的 I/O，可以从 EMC 技术支持人员那里获取帮助，因为这些 I/O 活动发生在 EMC 内部高速缓存的下面，Performance Monitor 无法看到。EMC 存储单元具有内部监视工具，此工具允许 EMC 技术支持工程师监视 Symmetrix 内部的 I/O 统计信息。Performance Monitor 只能通过从某个 SA 通道出来的 I/O 看到进出于 EMC 存储单元的 I/O。这一信息足以说明某个特定的 SA 通道正在对磁盘 I/O 请求进行排队，但是无法识别哪个磁盘或哪些磁盘引起磁盘排队。如果某个 SA 通道正在排队，并不一定说明瓶颈是由此 SA 通道引起的，因为引起问题的也有可能是磁盘驱动器（而且此可能性更高）。在 SA 通道和 DA 通道 + 驱动器之间隔离磁盘 I/O 瓶颈的一种方法是在托管服务器中再添加一个 SCSI 卡，并将它连接到另一个 SA 通道。如果 Performance Monitor 显示这两个 SA 通道中的 I/O 流量没有改变，并且磁盘队列依然存在，则说明瓶颈问题不是由 SA 通道引起的。隔离 I/O 瓶颈的另一种方法是使 EMC 工程师通过 EMC 监视工具监视 EMC 系统，并分析瓶颈是由哪些驱动器或 DA 通道引起的。
将 SQL Server 活动在尽可能多的可用磁盘驱动器中平均分配。如果处理支持大量 I/O 的小型数据库，需仔细考虑超级卷的大小以使 EMC 技术工程师进行定义。假定 SQL Server 由一个 30 GB 的数据库组成，EMC 硬盘可以提供 23 GB 的容量，因此，将整个数据库放在两个驱动器上是有可能的。从易管理和节省成本的角度出发，这种方法看上去可能比较有吸引力，但是若从 I/O 性能的角度来看，则不然。一个 EMC 存储单元可能有 100 个以上的内部驱动器要处理。SQL Server 中仅涉及两个驱动器可能引起 I/O 瓶颈。可以考虑定义小型超级卷，每个大概有 2 GB。则可能有大约 12 个超级卷与给定的 23 GB 硬盘相关。假定需要 15 个 2 GB 的超级卷存储数据库。确保每个超级卷与单独的物理硬盘相关。不要在一个物理驱动器中使用 12 个超级卷，然后在另一个物理驱动器中使用另外 3 个超级卷，因为这与使用两个物理驱动器同理（两个驱动器中具有 150 个不连续 I/O 或 300 个连续 I/O）。但是，如果使用 15 个超级卷，且每个都与单独的物理驱动器相关，SQL Server 就可以用 15 个物理驱动器来提供 I/O（15 个驱动器中每秒钟有 1,125 不连续的 I/O 活动或 2,250 个连续的 I/O 活动）。
同时考虑使用托管服务器中的几个 SA 通道以便在控制器中分配 I/O 工作。这对于支持多个 PCI 总线的托管服务器来说很有意义。本例中，考虑在每个托管服务器 PCI 总线中使用一个 SA 通道以便将 I/O 工作在 PCI 总线和 SA 通道中分配。在 EMC 存储系统中，每个 SA 通道与特定的 DA 通道相关，因此产生了特定的物理硬盘组。因为 SA 通道在 EMC 内部高速缓存中读写数据，所以不可能成为 I/O 瓶颈。记住 SCSI 控制器不可能发生瓶颈，所以最好的办法可能是集中时间在物理驱动器中平衡的 SQL Server 活动，而不必过多担心使用的 SA 通道的数量。
查找其它信息

Microsoft SQL Server Books Online 提供有关 SQL Server 构架和数据库优化的信息，同时提供完整的命令语法和管理文档。可以从 SQL Server 安装媒体中将 SQL Server Books Online 安装在任何 SQL Server 客户机或服务器中。建议在频繁使用 SQL Server 的机器上将 SQL Server Books Online 安装在硬盘上以便于使用。