《MS SQL Server 2000管理员手册》系列——36. 解决常见的效能问题[3]

载过量。另一方面，当数据文件执行随机性 I/O 时，就像数据文件一样，相同的磁盘可能只有每秒 85 次 I/O。如果系统需要执行更高的 I/O，那么等待时间将变得很长。
要确定是否过度使用磁盘驱动器，请使用效能监视器对象 PhysicalDisk 和 LogicalDisk监控计数器（稍后将在本节中讨论）。计数器收集关于实体和逻辑磁盘 I/O 行为的数据，例如每秒磁盘中发生的读取和写入。您必须使用 Windows NT/2000 指令 DISKPERF 启动磁盘效能计数器。这些计数器在安装操作系统时可用，但您应该知道如何启动与关闭计数器。计数器在收集数据时会占用系统资源，如 CPU 的时间，因此您应该只在要监视系统 I/O 时使用它们。您可使用 Windows NT/2000 指令 DISKPERF 来启动或关闭计数器。
要知道 DISKPERF 是否启动，请在命令提示字符下键入下列指令：
diskperf
如果 DISKPERF 已经启动，您将看到这个讯息："本系统的实体磁盘效能计数器已设为启动"。如果 DISKPERF 没有开启，您将看到这个讯息："本系统的逻辑和实体磁盘效能计数器现在设定为不启动。"
要启动 DISKPERF，请在命令提示字符下键入下列指令：
diskperf -Y
要停用 DISKPERF，请执行该指令：
diskperf -N
在 DISKPERF 生效前，您必须重新启动计算机。键入下面指令来看更多的DISKPERF 选项：
diskperf ?
有特殊重要性的计数器是 Disk Writes/Sec、Disk Reads/Sec、Avg. Disk Queue Length、Avg. Disk Sec/Write和Avg. Disk Sec/Read。这些计数器可以帮助您确定磁盘子系统是否过度使用。PhysicalDisk 和 LogicalDisk 对象都提供这些计数器。如果要详细检视这些计数器提供的信息，当增加计数器时，请在 新增计数器 对话框中按一下 说明 。
我们来看一个使用计数器的例子。假设您的系统有一个 I/O 瓶颈，您将检查PhysicalDisk 对象中的 Avg. Disk Sec/Transfer、Avg. Disk Sec/Read和Avg. Disk Sec/Write 计数器，因为它们监视磁盘需要执行读写的时间量，过长的等待时间表示您过度使用磁盘驱动器或磁盘阵列。一般的规则是这些计数器的正常读数在千分之 1 到 15 秒（0.001到0.015）之间，但是在尖峰时期可能达到千分之 20 秒（0.020）。如果您观察到高于千分之 20 秒的数值，您可能会遇到一个 I/O 子系统效能问题。
同时也检查 Disk Writes/Sec 和 Disk Reads/Sec。让我们假定这些值计数器显示每个磁盘每秒 20 次写入和 20 次读取，总共 40 次 I/O，且容量为每秒 85 次 I/O。同时如果您的磁盘等待时间较长的话，磁盘驱动器就可能故障了。另一方面，如果磁盘执行每秒 100 次 I/O，且等待时间大约千分之 20 秒或更多，则您需要增加磁盘来提高效能。
使用 RAID 数组时，如果您想确定您的系统正在执行的 I/O 有多少的话，就将在效能监视器中看到的每秒 I/O 次数除以数组中磁盘的总数量和 RAID 资源占用的因子。下表列出了使用 RAID 技术时产生读写的实体 I/O 次数。
表36-1　每个RAID层级读写执行的实体I/O次数
RAID层级  读取  写入
0 1 1
1或10 1 2
5 1 4
一般情况下，更正 I/O 子系统瓶颈的最佳途径是增加更多的磁盘。但是请记得考虑 I/O 瓶颈的其它可能原因，例如较低的快取命中速率以及执行交易的 I/O 太多。（在大多数情况下，快取命中速率低于 90% 就太低了。）如果您发现了一个 I/O 瓶颈，请回顾 第 6 章 中的说明，来确定您系统需要的磁盘数量。
故障组件
　
有时您的系统可能遇到由故障组件引起的效能问题。如果故障组件没有完全失效而仅仅是效能降低，那这可能是一个很难排除的问题，因为问题的多样性和复杂性以及它的解决方案已经超出了本书的范围。但是这里有几个辨识故障组件问题的诀窍：
•  比较磁盘和数组 当在效能监视器中检视统计资料时，比较相似的组件。如果您发现两个磁盘执行 I/O 的速率相同，却显示不同的等待时间，那么较慢的磁盘可能遇到了问题。
　
•  监控指示灯 网络集线器通常有碰撞指示灯。如果您注意到某个网络区段出现了异常的高碰撞，这时您可能有故障组件，也许是网络卡或是网络缆线。
　
•  了解您的系统 您花在系统上的时间越多，您越能了解系统的特性。当系统不正常运作时您就会发现。
　
•  使用效能监视器 这是一个监控系统行为的好方法。
　
•  阅读记录文件 养成常常检查 SQL Server 和 Windows2000 事件检视（Event Viewer）系统和应用程序记录的习惯。每天回顾这些记录文件可以在问题失控前发现问题。
　
应用程序
　
造成效能问题的另一个系统组件是 SQL Server 应用程序。这些问题可能发生在应用程序代码或在应用程序执行时的 SQL 陈述式中。本节提供一些提示和准则，用来解决和 SQL Server 应用程序有关的效能问题。
最佳化执行计划
　
正如我们在 第 35 章 所看到的，选择一个最佳的执行计划和数据存取方法对于查询是很重要的。不幸的是，并没有一定的公式来确定最佳的计划。SQL Server 自动选择查询最佳化器的计算为最佳的执行计划。随着您熟悉联结操作的不同类型，您可能可以透过分析以确定最佳执行计划。通常您需要试过各种不同的计划后，才能找到最佳方法。
使用索引
　
正如我们在 第 17 章 和 第 35 章 所看到的，正确索引的使用对于好的效能是十分重要的。使用索引来找出所需的数据，这只需要 10 到 20 个 I/O 操作，而透过数据表扫描找出所需的数据，则可能需要成千上万的 I/O 操作。不过必须谨慎地使用索引。记住，在使用 INSERT、UPDATE 或 DELETE 陈述式修改数据表的各种数据时，索引将自动更新，显示更改后的数据，因此会产生比平常更多的 I/O 操作。注意不要建立太多索引，否则用来维护这些索引的资源占用可能会影响数据修改的效能。
使用预存程序
　
正如我们在 第 21 章 中所看到的，预存程序被用来在服务器上执行预先包装和预先编译的SQL陈述式。从应用程序呼叫预存程序对效能有所帮助，因为改善了服务器上 SQL 陈述式的可重复使用性，也减少了网络的流量。由于预存程序执行在服务器上，可以减少客户端和服务器之间传送的数据数量，您可以在预存程序中将程序程序化并将数据筛选处理，而不用在应用程序中执行这些动作。
SQL Server 组态设定
　
SQL Server 2000 使用虚拟方式自行调整组态，但是有些调整参数仍然可以用来变更您的系统操作和执行的方式。在本节中，您将学到如何设定这些选项，以及它们如何影响到您系统的操作。在大多数情况下，是不需要更改这些参数的，但是了解它们是什么以及它们做什么使您有机会决定是否更改它们。您可以使用 Enterprise Manager 或 sp_configure 来设定。
要使用 Enterprise Manager，请在您要设定的服务器名称上按右键并从快捷菜单中选择 内容 ，显示 SQL Server属性 窗口。这个窗口包含9个页签，每个页签都有您可以设定的选项。下面将说明这些页签以及它们的选项。
当使用 sp_configure 设定这些选项时，某些选项是设定为进阶（advanced）的。（下面几节将说明哪些是进阶选项。）您必须将 show advanced options 选项设定为1（启用），这样才可以使用 sp_configure 来更改一个进阶选项。预设情况下该选项设定为0（禁用）。（当使用 Enterprise Manger 来设定进阶选项时，您不需要担心这个选项。）要设定 show advance options，请使用下面的陈述式：
sp_configure "show advanced options", 1
GO
通常，要使用 sp_configure 设定某个选项，请使用下面的语法：
sp configure "option name", value
affinity mask 选项
　
affinity mask 选项是用来指定在多处理器环境中，SQL Server 执行绪可以在哪些CPU 上执行。默认值为0，指定由 Windows 2000 排程算法来决定执行绪关连性。非零值设定了一个 bitmap，用来定义可以执行 SQL Server 的 CPU。十进制数值1（或二进制位屏蔽值00000001）指明只使用CPU 1、2（或00000010）指明只使用CPU 2、3（或00000011）指明使用CPU 1和CPU 2，等等。
这个选项是进阶选项，表示在使用 sp_configure 设定该选项时，必须设定 show advanced options 为1。它也可以用 Enterprise Manager 来设定。要这样做，请选择 SQL Server属性 窗口中的 处理器 页签，在 处理器控制 区域，选择您要 SQL Server 使用的 CPU 旁的复选框。按一下 套用 和 确定 来储存更改。您必须停止并重新启动 SQL Server，使该更改生效。
在指定的 SQL Server 系统上，您必须设定 affinity mask 选项，允许 SQL Server使用所有的 CPU。在没有指定的 SQL Server 系统上（包括需要 CPU 时间的其它处理程序），您可能要试着设定 affinity mask，这样 SQL Server 才会留下一个 CPU 不用而用其余的 CPU。
lightweight pooling 选项
　
lightweight pooling 选项用来设定 SQL Server 使用 lightweight 执行绪或细微模式（fibers）。使用细微模式可以减少内容切换（context switches），允许 SQL Server（而不使用 Windows NT 或 Windows 2000 的排程程序）掌控排程。如果您的应用程序在多处理器系统中执行，且您看到了大量的内容切换，您可能需要将 lightweight pooling 参数设定为1，这将启用 lightweight pooling，然后监控内容切换的数量，来核对它们是否已经减少了。默认值为 0，这样会禁用细微模式。
lightweight pooling选项也是一个进阶选项，在使用sp_configure 设定该选项时，必须将show advanced options设定为1。它也可以用Enterprise Manager来设定。选择 SQL Server属性 窗口中的 处理器 页签，在 处理器控制 区域，选