软件测试中数据仓库的 RDBMS 性能优化指南

同等的图形化执行计划输出

下图显示查询 1 的图形化执行计划。

如果您的浏览器不支持嵌入式框架，请单击此处在单独的页中查看。

该执行计划使用表扫描来解析查询 1。要从小表检索信息，最有效的方法是使用表扫描。但在大表上，由执行计划指明的表扫描实际是一种警告，它说明表需要最好的索引，或者现有索引的统计信息需要更新。您可以使用 UPDATE STATISTICS 命令在表或索引上更新统计信息。如果启发式页与基础索引值的同步差异过大，SQL Server 将自动更新索引。例如，如果您从 testtable 中删除了所有包含 ckey1 值等于“b”的行，然后，没有先更新统计信息就运行查询。最好让 SQL Server 自动维护索引统计信息，因为它有助于确保查询始终能够使用完好的索引统计信息。如果使用 ALTER DATABASE 语句将 AUTO_UPDATE_STATISTICS 数据库选项设为 OFF，则 SQL Server 不会自动更新统计信息。

查询 2

select nkey1,col2 from testtable where nkey1 = 5000


基于文本的执行计划输出

--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([TraceDB].[dbo].[testtable]))
|--Index Seek(OBJECT:([TraceDB].[dbo].[testtable].[testtable2]),
SEEK:([testtable].[nkey1]=Convert([@1])) ORDERED FORWARD)


同等的图形化执行计划输出

下面两图显示查询 2 的图形化执行计划。

如果您的浏览器不支持嵌入式框架，请单击此处在单独的页中查看。

如果您的浏览器不支持嵌入式框架，请单击此处在单独的页中查看。

查询 2 的执行计划在 nkey1 列上使用非聚集索引。这是由 nkey1 列上的 Index Seek 操作指明的。Bookmark Lookup 操作指明 SQL Server 需要执行指针跳转，从表的索引页跳转到数据页，以检索请求的数据。需要执行指针跳转的原因是查询要求查找 col2 列，而非聚集索引内不包含该列。

查询 3

select nkey1 from testtable where nkey1 = 5000


基于文本的执行计划输出

|--Index Seek(OBJECT:([TraceDB].[dbo].[testtable].[testtable2]),
SEEK:([testtable].[nkey1]=Convert([@1])) ORDERED FORWARD)


同等的图形化执行计划输出

下图显示查询 3 的图形化执行计划。

如果您的浏览器不支持嵌入式框架，请单击此处在单独的页中查看。

查询 3 的执行计划使用 nkey1 上的非聚集索引作为覆盖索引。请注意，该查询不需要执行 Bookmark Lookup 操作。原因是该查询（SELECT 和 WHERE 子句）所需的全部信息都由非聚集索引提供。这就是说，非聚集索引页中不需要有指向数据页的指针跳转。与需要书签查找的情况相比，I/O 有所减少。

系统监视

系统监视器提供大量的有关数据库服务器执行期间所发生的 Windows 和 SQL Server 操作的信息。

在系统监视器的图形模式下，请注意最大和最小值。因为过大和过小的数据点都会使平均值失真，所以对过于强调平均值的情况一定要小心。研究图形的形状并与最小和最大值比较，以便准确地理解行为。使用 BACKSPACE 键，用一条白线突出显示计数器。

您可以使用系统监视器在日志文件中记录所有可用的 Windows 和 SQL Server 系统监视器对象/计数器，而同时以交互方式查看系统监视器（图表模式）。采样间隔的设置决定了日志文件增大的速度。日志文件可能很快变大（例如，如果打开所有计数器，采样间隔设为 15 秒，日志文件在 1 小时内就能达到 100 兆字节）。测试服务器上最好有足够的空闲千兆字节来存储这些类型的文件。不过，如果保留空间对您很重要，请尝试采用较长的日志间隔，以免系统监视器过于频繁地对系统采样。请尝试 30 或 60 秒。这样，系统监视器会以合理的频率对所有计数器重新采样，同时又能保持较小的日志文件大小。

系统监视器也会耗用少量 CPU 资源和磁盘 I/O 资源。如果系统没有多余的备用磁盘 I/O 和/或 CPU，请考虑从另一台计算机运行系统监视器，然后通过网络监视 SQL Server。在通过网络监视时，请只使用图形模式。与通过局域网发送信息相比，在 SQL Server 本地记录性能监视信息的效率往往会更高。如果您必须通过网络记录日志信息，可以只记录最重要的计数器信息。

在性能测试运行期间，将所有可用计数器的信息记录到某个文件中供以后分析，这不失为一个好做法。这样，对于任何计数器，以后都可以再作进一步检查。您可以配置系统监视器将所有计数器记录到日志文件中，与此同时，在其他某种模式（如图形模式）下监视最感兴趣的计数器。这样，在性能运行期间，所有信息都会被记录下来，但您最关注的计数器会以清晰整洁的系统监视器图形显示出来。

设置要记录的系统监视器会话

1. 从 Windows 2000 开始菜单中，指向程序、管理工具，然后单击性能，打开系统监视器。
2. 双击性能日志和警报，然后单击计数器日志。
3. 现有的日志都会在详细信息窗格中列出。绿色图标表示日志正在运行；红色图标表示日志已被停止。
4. 右键单击详细信息窗格的空白区域，然后单击新日志设置。
5. 在名称中键入日志的名称，然后单击确定。
6. 在常规选项卡上，单击添加。选择要记录的计数器。您在此处确定要在会话期间监视的 SQL Server 计数器。
7. 如果要更改默认文件，请在日志文件选项卡上进行更改。
8. 记录的会话可以设置为按预定义的时间段自动运行。为此，请在调度选项卡上修改调度信息。

注意 要保存日志文件的计数器设置，请用右键单击详细信息窗格中的文件，然后单击将设置另存为。然后，指定用来保存这些设置的 .htm 文件。要在新日志中重用已保存的设置，请用右键单击详细信息窗格，然后单击新日志设置来自。

启动已记录的监视会话

1. 从 Windows 2000 开始菜单中，指向程序、管理工具，然后选择性能，打开系统监视器。
2. 双击性能日志和警报，然后单击计数器日志。
3. 右键单击要运行的计数器日志，然后选择启动。
4. 现有的日志都会在详细信息窗格中列出。绿色图标表示日志正在运行；红色图标表示日志已被停止。

停止已记录的监视会话

1. 从 Windows 2000 开始菜单中，指向程序、管理工具，然后选择性能，打开系统监视器。
2. 双击性能日志和警报，然后单击计数器日志。
3. 右键单击要运行的计数器日志，然后选择停止。

从已记录的监视会话向系统监视器装载数据供分析使用

1. 从 Windows 2000 开始菜单中，指向程序、管理工具，然后选择性能，打开系统监视器。
2. 单击系统监视器。
3. 右键单击系统监视器的详细信息窗格，然后单击属性。
4. 单击源选项卡。
5. 在数据源下，单击日志文件，然后键入文件路径，或单击浏览，查找所需的日志文件。
6. 单击时间区间。要在日志文件中指定希望查看的时间区间，请拖动滑动条或滑动条柄，设置相应的开始和结束时间。
7. 单击数据选项卡，然后单击添加，打开添加计数器对话框。您在日志配置期间选择的计数器会显示出来。您可以在图形中包括所有这些计数器或其中一部分。

如何使系统监视器记录的事件与过去的某个时点相关

• 从系统监视器会话中，右键单击系统监视器的详细信息窗格，然后单击属性。时间区间和滑动条允许您设定要在图形中查看的开始、当前和结束时间。

需要监视的关键性能计数器

有几个性能计数器提供了有关以下重要方面的信息：内存、分页、处理器、I/O 和磁盘活动。

监视内存

默认情况下，SQL Server 会根据可用系统资源动态更改其内存需求。如果 SQL Server 需要更多内存，它会查询操作系统，以确定是否有可用的空闲物理内存，并使用可用内存。如果 SQL Server 当前不需要分配给它的内存，它将向操作系统释放内存。不过，动态使用内存的选项会被服务器配置选项替代，这些选项是最小服务器内存、最大服务器内存和设置工作集大小。有关更多信息，请参阅“SQL Server 联机丛书”。

要监视由 SQL Server 使用的内存量，请检查下列性能计数器：

• 进程：工作集
• SQL Server：缓冲管理器：缓存命中率
• SQL Server：缓冲管理器：全部页
• SQL Server：内存管理器：总的服务器内存 (KB)

工作集计数器显示由进程使用的内存量。如果该数字一直低于 SQL Server 配置使用的内存量（由服务器选项最小服务器内存和最大服务器内存设置），说明为 SQL Server 配置的内存比它实际需要的内存多。否则，使用设置工作集大小服务器选项调整工作集的大小。

缓存命中率计数器是特定于应用程序的；不过，该比率达到或超过 90% 比较理想。请增多内存，直到该值稳定地达到 90% 以上，这样就表明数据缓存满足了 90% 以上的数据请求。

如果与计算机中的物理内存量相比，总的服务器内存 (KB) 计数器值一直较高，说明需要更多内存。

强制分页

如果内存：页/秒大于零或内存：页读取/秒大于五，说明 Windows 正在使用磁盘来解决内存引用（强制分页错误）。它耗用磁盘 I/O + CPU 资源。内存：页/秒清楚地指明了 Windows 正在执行的分页量，以及数据库服务器当前的 RAM 配置是否够用。系统监视器中的强制分页信息有一个子集记录的是 Windows 为了解决内存引用而必须读取分页文件的次数/秒，它由内存：页读取/秒表示。如果内存：页读取/秒大于 5，则对性能不利。

为了避免分页，SQL Server 自动内存优化将尝试动态调整 SQL Server 对内存的使用。每秒读取少量页是正常的，但如果分页过多，则需采取更正措施。

如果 SQL Server 自动优化内存，您可以选择添加更多 RAM 或从数据库服务器中删除其他应用程序，以帮助内存：页/秒达到合理水平。

如果 SQL Server 内存是在数据库服务器上手动配置的，则需要减少指定给 SQL Server 的内存，从数据库服务器中删除其他应用程序，或向数据库服务器添加更多 RAM。

保持内存：页/秒为零或接近零，对数据库服务器性能有利。这就是说，Windows 及其所有应用程序（包括 SQL Server）不会为满足内存请求中的任何数据而转到分页文件，所以服务器上的 RAM 是充足的。页/秒略大于零尚可接受，但请记住，每次从分页文件（而非 RAM）检索数据时，都将遭受相对较高的性能惩罚（磁盘 I/O）。

对与 Windows 分页文件相关的所有驱动器间的内存：页输入/秒与逻辑磁盘：磁盘读取/秒以及内存：页输出/秒与逻辑磁盘：磁盘写入/秒进行比较是很有用的，因为通过它们可以知道真正与分页而非其他应用程序（即 SQL Server）相关的磁盘 I/O 量。隔离分页文件 I/O 活动的另一种便捷方式是确保分页文件与所有其他 SQL Server 文件不在同一组驱动器上。将分页文件与 SQL Server 文件隔开也对磁盘 I/O 性能有利，因为它允许与分页相关的磁盘 I/O 和与 SQL Server 相关的磁盘 I/O 并行执行。

软分页

如果内存：分页错误/秒大于零，说明 Windows 正在分页，但计数器中既有强制分页，也有软分页。我们已在上一节讨论过强制分页。软分页表示数据库服务器上的应用程序正在请求的内存页仍然位于 RAM 以内，但已位于 Windows 工作集之外。内存：分页错误/秒有助于获得正在发生的软分页量。系统中没有称为“软分页错误/秒”的计数器。您可以使用此公式计算每秒钟发生的软分页错误数：内存：分页错误/秒 - 内存：页输入/秒 = 软分页错误/秒

要确定导致过多分页的是否是 SQL Server 而不是其他进程，请监视 SQL Server 进程的进程：分页错误/秒计数器，并注意相关 Sqlservr.exe 实例的分页错误数/秒是否接近内存：页/秒的数值。

与硬分页错误相比，软分页错误对性能的不利影响通常要小一些，因为它们耗用 CPU 资源。硬分页错误耗用磁盘 I/O 资源。获得良好性能的最佳环境是杜绝任何类型的错误。

注意 在 SQL Server 第一次访问它所有的数据缓存页时，对每一页的第一次访问均会导致软分页错误。在 SQL Server 第一次启动和第一次使用数据缓存时，不必担心最初的软分页错误。

监视处理器

您的目标应该是：尽可能充分地利用所有分配给服务器的处理器，以获得最佳性能，而同时又避免因过于繁忙而出现处理器瓶颈。性能优化所面临的挑战是：如果 CPU 不是瓶颈，总有其他东西是瓶颈（很可能是磁盘子系统），因而浪费了 CPU 容量。通常，CPU 是最难扩展的资源（某些配置特定的级别除外，例如，许多最新系统上的 4 CPU 或 8 CPU），因此，如果繁忙系统上的 CPU 使用率超过 95%，说明系统运行良好。同时，您应该监视事务的响应时间，确保响应时间合理；如果响应时间不合理，而 CPU 使用率超过 95%，则可能说明可用 CPU 资源承担的工作负荷过多，您要么增加 CPU 资源，要么减少或优化工作负荷。

请查看系统监视器计数器处理器：% 处理器时间，确保每个 CPU 上的处理器使用率一直低于 95%。系统：处理器队列长度是 Windows 系统上所有 CPU 的处理器队列。如果每个 CPU 的系统：处理器队列长度大于二，则说明出现了 CPU 瓶颈。在检测到 CPU 瓶颈时，您需要向服务器添加处理器，或减少系统上的工作负荷。减少工作负荷的方法是：通过优化查询或改进索引来减少 I/O，从而减少 CPU 使用率。

在怀疑出现 CPU 瓶颈时需要监视的另一个系统监视器计数器是系统：上下文切换/秒，因为它指明了 Windows 和 SQL Server 必须从在一个线程上执行切换到在另一个线程上拖动的频率（次数/秒）。它耗用 CPU 资源。上下文切换是多线程、多处理器环境的正常组件，但过多的上下文切换会降低系统性能。应对方法是在有处理器队列时，只关注上下文切换。

如果观察处理器队列，则将上下文切换级别用作 SQL Server 性能优化的尺度。如果看起来是上下文切换导致出现瓶颈，您可以考虑两种方法：使用关系掩码选项，使用基于纤程的调度。

使用关系掩码选项可以提高重负荷下运行的对称多处理器 (SMP) 系统（微处理器数量超过四个）的性能。您可以使线程与特定处理器相关，并指定 SQL Server 将使用的处理器。您还可以使用关系掩码选项设置来阻止 SQL Server 活动使用某些处理器。在更改关系掩码的设置之前，请记住，Windows 会将与 NIC 的相关延迟进程调用 (DPC) 活动分配给系统中编号最高的处理器。在安装并激活了多个 NIC 的系统中，另外每增加一个卡的活动，就会分配给下一个编号最高的处理器。例如，安装了两个 NIC 的八处理器系统将每个 NIC 的 DPC 分配给处理器 7 和处理器 6（从 0 开始计数）。在使用轻量池选项时，SQL Server 切换到基于纤程的调度模式，而不是默认的基于线程的调度模式。纤程本质上是轻量线程。使用命令 sp_configure 'lightweight pooling',1 可启用基于纤程的调度。

通过监视处理器队列和上下文切换，您可以监视设置关系掩码和轻量池的值之后产生的效果。某些情况下，这些设置非但不会改进性能，反而会使性能下降。另外，除非系统中有四个或更多个处理器，否则它们一般不会带来很大的收益。DBCC SQLPERF (THREADS) 提供了映射回 SPID 的有关 I/O、内存以及 CPU 使用率的更多信息。执行下面的 SQL 查询可调查当前最耗用 CPU 时间的使用者：

select * from master.sysprocesses order by cpu desc


监视处理器队列长度

如果系统：处理器队列长度大于二，说明服务器的处理器收到的工作请求多于它们能够以一个组的方式集体处理的请求。因此，Windows 需要将这些请求放在队列中。

某些处理器队列说明 SQL Server 的总体 I/O 性能良好。如果没有处理器队列，并且 CPU 使用率低，说明系统某处可能出现了性能瓶颈，最有可能的地方就是磁盘子系统。处理器队列中留有合理的工作请求量，这说明 CPU 并不空闲，系统的其余部分也与 CPU 保持同步。

根据一般经验，理想的处理器队列数是数据库服务器中的 CPU 数乘以二。

如果处理器队列数明显高于该值，可能表明服务器遇到了 CPU 瓶颈，您需要进行调查。过多的处理器队列会耗用查询执行时间。多个不同活动可能导致出现处理器队列。消除强制分页和软分页有助于节省 CPU 资源。其他有助于减少处理器队列的方法包括：优化 SQL 查询、挑选更好的索引以减少磁盘 I/O（从而减少 CPU 使用量）、在系统中添加更多 CPU（处理器）。

监视 I/O

磁盘写入字节/秒和磁盘读取字节/秒计数器表明磁盘的数据吞吐量，以每个逻辑驱动器或物理驱动器每秒的字节数计。请仔细地将这些数字与磁盘读取/秒和磁盘写入/秒均衡比较。不要看到较低的字节数/秒，就相信磁盘 I/O 子系统不忙。

监视与 SQL Server 文件相关的所有驱动器的磁盘队列长度，然后确定哪些文件与过多的磁盘队列相关。

如果系统监视器表明某些驱动器不如其他驱动器繁忙，则可将 SQL Server 文件从出现瓶颈的驱动器移到不太繁忙的驱动器。这样有助于将磁盘 I/O 活动更均匀地分布到各个硬盘。如果将一个大型驱动器池用于 SQL Server 文件，磁盘队列的解决方法是在池中添加更多的物理驱动器，从而增大驱动器池的 I/O 容量。

出现磁盘队列可能表明某个 SCSI 通道中的 I/O 请求数量已达到饱和。系统监视器无法直接确定该情况是否属实。存储器供应商通常会另外提供工具，以帮助监视 RAID 控制器所服务的 I/O 数量，以及控制器是否在对 I/O 请求进行排队。如果 SCSI 通道上连接了许多磁盘驱动器（十个或更多个），并且所有驱动器都以全速执行 I/O，则更有可能发生该情况。应对此情况的解决方案是：将一半磁盘驱动器连接到另一个 SCSI 通道或 RAID 控制器，以平衡该 I/O。通常，在 SCSI 通道之间重新平衡驱动器需要重建 RAID 阵列以及完全备份/还原 SQL Server 数据库文件。

磁盘时间百分比

在系统监视器中，物理磁盘：% 磁盘时间和逻辑磁盘：% 磁盘时间计数器监视磁盘因读取/写入活动而处于繁忙状态的时间百分比。如果 % 磁盘时间计数器很高（超过 90%），请检查当前磁盘队列长度计数器，以查看有多少系统请求正在等待磁盘访问。等待 I/O 的请求数量应该始终不超过构成物理磁盘的轴数量的 1.5 至 2 倍。大多数磁盘只有一个轴，然而不昂贵的磁盘冗余阵列 (RAID) 设备通常有多个轴。硬件 RAID 设备在系统监视器中显示为一个物理磁盘；通过软件创建的 RAID 设备显示为多个实例。

磁盘队列长度

监视过长的磁盘队列是一项重要任务。

要监视磁盘队列长度，您需要观察多个系统监视器磁盘计数器。要启用这些计数器，请从 Windows 2000 或 Windows NT 命令窗口运行 diskperf –y 命令，然后重新启动计算机。

出现磁盘队列的物理硬盘驱动器将在弥补 I/O 处理的同时阻止磁盘 I/O 请求。这些驱动器上的 SQL Server 响应时间也不如从前。此操作会耗用查询执行时间。

如果使用 RAID，为了计算每个物理驱动器的磁盘队列，您需要了解有多少个物理硬盘驱动器与每个 Windows 视为单个物理驱动器的驱动器阵列相关。为了了解每个物理驱动器保存 SQL Server 数据的具体方式，以及每个 SCSI 通道上分发的 SQL Server 数据量，请向硬件专家咨询，让他们来解释 SCSI 通道和物理驱动器分发。

通过系统监视器查看磁盘队列的选择有多种。逻辑磁盘计数器与通过磁盘管理器分配的逻辑驱动器盘符相关，而物理磁盘计数器与磁盘管理器视为一个物理磁盘设备的内容相关。请注意，磁盘管理器视为一个物理设备的驱动器可能是一个硬盘驱动器，也可能是一个包含多个硬盘驱动器的 RAID 阵列。当前磁盘队列长度是对磁盘队列的即时度量，而平均磁盘队列长度是采样期间磁盘队列度量的平均值。如果指明出现以下任一情况，请加以注意：

• 逻辑磁盘：平均磁盘队列长度 > 2
• 物理磁盘：平均磁盘队列长度 > 2
• 逻辑磁盘：当前磁盘队列长度 > 2
• 物理磁盘：当前磁盘队列长度 > 2

这些建议的度量适于每个物理硬盘驱动器。如果 RAID 阵列与磁盘队列度量相关，则需要用该度量除以 RAID 阵列中的物理硬盘驱动器的数量，以确定每个物理硬盘驱动器的磁盘队列。

注意 在保存 SQL Server 日志文件的物理硬盘驱动器或 RAID 阵列上，磁盘队列不是有用的度量方法，因为日志管理器不会对多个针对 SQL Server 日志文件的 I/O 请求进行排队。

了解 SQL Server 技术内幕

了解 SQL Server 2000 的一些技术内幕有助于您管理数据库的性能。

工作线程

SQL Server 维护着一个 Windows 线程池，这些线程的作用是为成批提交到数据库服务器的 SQL Server 命令提供服务。sp_configure 选项最大工作线程的设置规定了可以为所有传入的命令批提供服务的线程（在 SQL Server 术语中称为工作线程）的总数。如果主动提交命令批的连接数大于指定的最大工作线程数，将在主动提交命令批的连接之间共享工作线程。许多安装都适合使用默认值 255。请注意，大部分连接大多数的时间都在等待从客户端接收命令批。

从 SQL Server 缓冲区缓存中写出 8 KB 脏页的任务主要由工作线程来完成。为了获得最佳性能，工作线程会异步调度它们的 I/O 操作。

惰性写入器

惰性写入器是在缓冲管理器内运行的 SQL Server 系统进程。惰性写入器刷新脏的旧缓冲（必须先将这些缓冲内所含的更改写入磁盘，随后才能将缓冲重新用于其他不同的页）批，然后将它们提供给用户进程。该活动有助于生成和维护可用的空闲缓冲，它们是大小为 8 KB，不含任何数据，可以重新使用的数据缓存页。在惰性写入器将每个 8 KB 缓存缓冲区刷新到磁盘上时，缓存页的标识会被初始化，这样，其他数据就可以写入空闲的缓冲区。惰性写入器在磁盘 I/O 量少时工作，从而将该活动对其他 SQL Server 操作的影响减到最小。

SQL Server 自动配置和管理空闲缓冲水平。性能计数器 SQL Server：缓冲管理器：惰性写入/秒指明了物理写出到磁盘的 8 KB 页的数量。请监视 SQL Server：缓冲管理器：可用页，查看该值是否下降。最佳状态是：惰性写入器使该计数器在所有 SQL Server 操作之间保持水平，这意味着惰性写入器与用户对空闲缓冲的需求保持同步。如果系统监视器对象 SQL Server：缓冲管理器：可用页的值达到零，说明用户负载有时需要较高水平的空闲缓冲，而惰性写入器无法提供这一水平的空闲缓冲。

如果惰性写入器难以使空闲缓冲保持稳定或至少保持在零以上，说明磁盘子系统可能无法提供足够的磁盘 I/O 性能。要证明是否确实如此，请将空闲缓冲水平的下降与磁盘队列作比较。解决办法是向数据库服务器磁盘子系统添加更多物理磁盘驱动器，以提高磁盘 I/O 处理能力。

在系统监视器中监视当前的磁盘队列水平，方法是查看逻辑磁盘或物理磁盘的性能计数器平均磁盘队列长度或当前磁盘队列长度，确保与任何 SQL Server 活动相关的每个物理驱动器的磁盘队列小于 2。对于使用硬件 RAID 控制器和磁盘阵列的数据库服务器，记住用“逻辑/物理磁盘”计数器报告的数字除以与该逻辑驱动器盘符或物理硬盘驱动器盘符（依据磁盘管理器的报告）相关的实际硬盘数量，因为 Windows 和 SQL Server 不知道与 RAID 控制器相连的物理硬盘驱动器的实际数量。为了正确地解释系统监视器报告的磁盘队列数量，一定要知道与 RAID 阵列控制器相关的驱动器数量。

有关更多信息，请参阅“SQL Server 联机丛书”。

检查点

SQL Server 的每个实例需要定期确保将所有脏日志和数据页刷新到磁盘。这称为检查点。在重新启动 SQL Server 的实例时，使用检查点可以减少从故障中恢复所需的时间和资源。在检查点期间，脏页（进入缓冲区缓存后已经过修改的缓冲区缓存页）会被写入 SQL Server 数据文件。在检查点处写入磁盘的缓冲仍然包含数据页，用户可以读取或更新该页，而不必从磁盘重新读取，这一点与惰性写入器创建的空闲缓冲不同。

检查点逻辑尝试让工作线程和惰性写入器负责大部分的脏页写出工作。为此，如有可能，检查点逻辑在写出脏页之前，尝试额外多等待一个检查点。这样，工作线程和惰性写入器就有更多的时间来写出脏页。某些情况下，检查点逻辑在写出脏页之前需要额外多等一段时间，有关这些情况的详细信息，请参阅“SQL Server 联机丛书”中的主题“检查点和日志的活动部分”。要请住的重点是，检查点逻辑会尝试通过等待额外的检查点在更长的时间段内均衡 SQL Server 磁盘 I/O 活动。

在有大量的数据页需要从缓存刷新到磁盘上时，为了使检查点操作更有效，SQL Server 将要刷新的数据页按照它们在磁盘上出现的顺序进行排序。这有助于尽量减少磁盘在缓存刷新过程中的来回移动，并在可能的情况下使用连续磁盘 I/O。检查点进程也向磁盘子系统异步提交 8 KB 磁盘 I/O 请求。这样，SQL Server 就能更快地完成对所需磁盘 I/O 请求的提交，因为检查点进程不必等待磁盘子系统发回指明已将数据实际写入磁盘的报告。

重要的一点是要监视与 SQL Server 数据文件相关的硬盘驱动器上的磁盘队列，确定 SQL Server 目前发送的 I/O 请求是否超过磁盘的实际处理能力；如果情况属实，必须提高磁盘子系统的磁盘 I/O 能力，使它能够处理负载。

日志管理器

像所有其他主流 RDBMS 产品一样，SQL Server 也可以确保在发生中断 SQL Server 联机状态的事件（例如，断电、磁盘驱动器有故障、数据中心起火，等等）时，数据库上执行的所有写入活动（插入、更新和删除）不会丢失。SQL Server 日志记录进程有助于确保可恢复性。在完成任何隐式（单个 SQL 查询）或显式事务（所定义的发出 BEGIN TRAN/COMMIT 或 ROLLBACK 命令序列的事务）之前，日志管理器必须从磁盘子系统收到信号，表明与该事务相关的所有数据更改均已成功写入相关的日志文件。这一规则可以确保：如果 SQL Server 因某种原因而突然关机，而检查点和惰性写入器尚未将写入数据缓存的事务刷新到数据文件，那么，SQL Server 可以在启动后读取和重新应用事务日志。恢复是指服务器停机之后读取事务日志以及向 SQL Server 数据应用事务。

由于在每个事务完成时，SQL Server 必须等待磁盘子系统完成对 SQL Server 日志文件的 I/O，所以包含 SQL Server 日志文件的磁盘要有足够的磁盘 I/O 处理能力来承受预期的事务负载，这一点很重要。

SQL Server 日志文件的相关磁盘队列的监视方法与 SQL Server 数据库文件的相关磁盘队列的监视方法不同。请使用系统监视器计数器 SQL Server：数据库 <数据库实例>：日志刷新等待时间和 SQL Server：数据库 <数据库实例>：日志刷新等待/秒来查看磁盘子系统上是否有处于等待完成状态的日志写入器请求。

具备缓存功能的控制器性能最高，但除非该控制器能够确保将它负责的数据最终写入磁盘，甚至在电源故障时也能最终写入磁盘，否则不得将它用于包含日志文件的磁盘。有关具备缓存功能的控制器的更多信息，请参阅本章的“硬件 RAID 控制器板载缓存的效果”一节。

预读管理

SQL Server 2000 为大规模连续读取表扫描等活动提供了自动管理功能。预读管理完全自行配置和自行优化，并且与

SQL Server 查询处理器的操作紧密地结合在一起。预读管理用于大表扫描、大索引区间扫描、探测聚集索引和非聚集索引二进制树，以及其他情况。原因是预读采用的是 64 KB I/O，与 8 KB I/O 相比，64 KB I/O 能使磁盘子系统达到更大的磁盘吞吐量。如果需要检索大量数据，SQL Server 就使用预读来获得最大的吞吐量。

SQL Server 使用简单有效的索引分配映射表 (IAM) 存储结构，该结构支持预读管理。IAM 是 SQL Server 用于记录扩展盘区位置的机制，即每 64 KB 扩展盘区包含八页数据或索引信息。每个 IAM 页是包含紧密打包（位映射）信息的 8 KB 页，这些信息指明哪些扩展盘区包含所需的数据。IAM 页的压缩特性加快了它们的读取速度，经常使用的 IAM 页还可以保留在缓冲区缓存中。

预读管理可以将来自查询处理器的查询信息与需要从 IAM 页读取的所有扩展分区的位置信息组合在一起，从而构成多个连续的读取请求。连续的 64 KB 磁盘读取提供优异的磁盘 I/O 性能。SQL Server：缓冲管理器：预读页/秒性能计数器提供有关预读管理的有效性及效率的信息。

SQL Server 2000 企业版根据现有内存量动态调整预读页的最大数量。在 SQL Server 2000 的所有其他版本中，该值固定不变。SQL Server 2000 企业版的另一改进之处是通常所说的“旋转木马式扫描”，它允许多个任务共享整个表扫描。如果 SQL 语句的执行计划要求扫描表中的数据页，并且关系数据库引擎检测到已经为另一个执行计划扫描过该表，那么数据库引擎在第二次扫描的当前位置将第二次扫描加入到第一次扫描中。数据库引擎每次读取一页，并将每一页的所有行同时传递到这两个执行计划。此操作会一直进行，直至到达表的结尾。此时，第一个执行计划具有完整的扫描结果，但第二个执行计划仍必须检索在它加入正在进行的扫描之前所发生的数据页。然后，为第二个执行计划执行的扫描会折返回表的第一个数据页，并且向前扫描至它加入第一次扫描的位置。用这种方式可以组合任意数量的扫描；数据库引擎将一直在所有数据页之间循环，直到完成所有扫描。

有关预读管理需要注意一点，那就是过多的预读会对总体性能不利，因为它在缓存内填入不需要的数据页，占用本应用于其他用途的 I/O 和 CPU。对于这一点，只能通过一般的性能优化来解决：优化所有 SQL 查询，尽量减少进入缓冲区缓存的页数量。它包括确保具备正确的索引并在使用这些索引。使用聚集索引可以获得有效的区间扫描，定义非聚集索引有助于快速定位单行或更小的行集。例如，如果您准备在表中只创建一个索引，并且该索引将用于提取单行或更小的行集，该索引应为聚集索引。从表面上看，聚集索引比非聚集索引的速度快。

其他性能主题

使用星型架构和雪花形架构的数据库设计

数据仓库使用维度建模来组织数据，以便进行分析。维度建模会生成星型架构和雪花架构，这样也就为数据仓库中经常执行的大量数据读取操作带来了性能效率。大量的数据（通常成千上万行）存储在事实数据表中，表内各行都很短，这就使存储需求和查询时间减到最少。业务事实数据的属性会非正常化为维度表，以最大程度地减少检索数据时的表联接数量。

有关数据仓库的数据库设计的讨论，请参阅第 17 章“数据仓库设计注意事项”。

在 Transact-SQL 查询中使用等价运算符

在 SQL 查询中使用非等价运算符将强制数据库使用表扫描来对非等价对象取值。如果经常对非常大的表运行这些查询，将会生成高 I/O。包含“NOT”运算符（!=、<>、!<、!>）的 WHERE 子句（如 WHERE <column_name> != some_value）将生成高 I/O。

如果需要运行此类查询，请尝试更改查询的结构，从其中消除 NOT 关键字。例如：

不使用：

select * from tableA where col1 != "value"


尝试使用：

select * from tableA where col1 < "value" and col1 > "value"


减少行集大小和通讯开销

使用 Microsoft ActiveX® 数据对象 (ADO)、远程数据对象 (RDO) 和数据访问对象 (DAO) 数据库 API 等易用界面的 SQL 数据库程序员需要考虑他们生成的结果集。

ADO、RDO 和 DAO 为程序员提供了极好的数据库开发界面，程序员即使没有太多的 SQL 编程经验也能实现丰富的 SQL 行集功能。如果程序员仔细考虑他们的应用程序返回到客户端的数据量，并且跟踪 SQL Server 索引的位置以及 SQL Server 数据的安排方式，就能避免性能问题。SQL 事件探查器、索引优化向导和图形化的执行计划都是非常有用的工具，它们可以帮助程序员精确定位和修复出现问题的查询。

在使用游标逻辑时，请选择最适合您的处理类型的游标。不同类型的游标开销也不同。您应该了解所要执行的是何种类型的操作（只读、只向前处理，等等），然后选择相应的游标类型。

寻找各种机会来减少返回的结果集的大小，方法包括在选择列表中消除不需要返回的列、只返回所需的行。这有助于减少 I/O 和 CPU 消耗。

使用多个语句

通过在数据库上执行处理，您可以减少结果集的大小，并避免在客户端和数据库服务器之间进行不必要的网络通讯。为了执行无法用单个 Transact-SQL 语句执行的处理，SQL Server 允许您将多个 Transact-SQL 语句以下列方式组合在一起。

下面的 SQL Server 功能使您可以对同时使用多个 Transact-SQL 语句的情况进行控制。

重用执行计划

如果 SQL Server 能够利用先前查询的现有执行计划，则可以提高性能。要促使 SQL Server 重用执行计划，开发人员可以做的工作有很多。Transact-SQL 语句应根据以下原则编写。

• 使用对象的完全限定名，例如表和视图。

  例如，请不要这样编写 SELECT 语句：

  SELECT * FROM Shippers WHERE ShipperID = 3


  而应使用 SQLBindParameter ODBC 函数（以使用 ODBC 为例）：

  SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3


• 使用参数化查询，并提供参数值，而不要指定存储过程参数值或直接在搜索条件谓词中指定值。使用 sp_executesql 中的参数替代，或使用 ADO、OLE DB、ODBC 和 DB-Library API 的参数绑定。

  例如，请不要这样编写 SELECT 语句：

  SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3


  而应使用 SQLBindParameter ODBC 函数（以使用 ODBC 为例），将参数标记 (?) 绑定到程序变量，并按下面这样编写 SELECT 语句：

  SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = ?


• 在 Transact-SQL 脚本、存储过程或触发器中，使用 sp_executesql 执行 SELECT 语句：

  DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
/* Build the SQL string. */
SET @SQLString =
N'SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = @ShipID'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@ShipID int'
/* Execute the string. */
SET @IntVariable = 3
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ShipID = @IntVariable


  如果要避免创建和维护单独的存储过程的开销，则可使用 sp_executesql。

对多个批处理重用执行计划

如果多个并发应用程序将用一组已知参数执行同一个批处理，请将该批处理实现为将由这些应用程序调用的存储过程。

在 ADO、OLE DB 或 ODBC 应用程序将会多次执行同一个批处理时，请使用执行该批处理的 PREPARE/EXECUTE 模型。使用绑定到程序变量的参数标记来提供所需的全部输入值，例如，在 UPDATE VALUES 子句或搜索条件谓词中使用的表达式。

维护列中的统计信息

SQL Server 允许创建与某个列中值的分布有关的统计信息，即使该列不是索引的一部分也不成问题。查询处理器可以使用该统计信息来确定评估查询的最佳策略。在您创建索引时，SQL Server 会自动存储与索引列中的值的分布有关的统计信息。除索引列外，如果 AUTO_CREATE_STATISTICS 数据库选项设置为 ON（默认设置），只要在谓词中使用了某个列，即使该列不在索引中，SQL Server 也会自动创建该列的统计信息。

随着列中数据的更改，索引和列的统计信息将会过时，从而导致查询优化程序所做的有关如何处理查询的决策也不如以前理想。随着表中数据的更改，SQL Server 会定期地自动更新此统计信息。采样是在数据页中随机进行的，而且是从统计信息所需的表中或列上最小的非聚集索引中采样。在从磁盘读取了数据页之后，该数据页上的所有行都会用来更新统计信息。更新统计信息的频率由列或索引中的数据量以及发生更改的数据量决定。

例如，某个表中包含 10,000 行，如果其中的 1,000 个索引值发生了更改，这时就可能需要更新该表的统计信息，因为这 1,000 个值可能代表了表中很大一部分数据。但是，对于包含 1000 万个索引条目的表而言，其中 1000 个索引值发生了更改就没有太大关系，因此可能不会自动更新统计信息。不过，SQL Server 始终会确保对最小数量的行进行采样；始终会对小于 8 MB 的表通过完全扫描来收集统计信息。

注意 使用 SQL 查询分析器以图形方式显示查询的执行计划时，将以示警的形式（表名用红色文字显示）指出统计信息过时或缺少统计信息。另外，使用 SQL 事件探查器监视缺少的列统计信息事件类，可以发现什么时候缺少统计信息。

通过使用 sp_createstats 系统存储过程，您可以使用单个语句，在当前数据库内的所有用户表中的所有适合的列上很轻松地创建统计信息。不适于创建统计信息的列包括：不确定的或不精确的计算列，或是数据类型为 image、text 和 ntext 的列。

如果手动创建统计信息，则您可以创建包含多个列密度（列组合重复的平均数）的统计信息。例如，某个查询包含以下子句：

WHERE a = 7 and b = 9


同时在两列（a 和 b）上创建手动统计信息可以使 SQL Server 更好地预估查询，因为统计信息也包含 a 和 b 列组合的非重复值的平均数。这样，SQL Server 就可以利用 col1 上建立的索引（此情况下最好为聚集索引），而不必进行表扫描。有关如何创建列统计信息的信息，请参阅“SQL Server 联机丛书”中的主题“创建统计信息”。

查找更多信息

• “SQL Server 联机丛书”提供了有关 SQL Server 结构和数据库优化的信息，同时还提供了完整的命令语法和管理的文档资料。“SQL Server 联机丛书”可以从 SQL Server 安装介质安装到任何 SQL Server 客户端或服务器计算机上。
• 有关 Microsoft SQL Server 的最新信息，包括有关 SQL Server 的技术论文，请访问下面的 Microsoft SQL Server Web 站点：
  • http://www.microsoft.com/china/sql/
  • http://www.microsoft.com/china/technet/sql/
  • http://msdn.microsoft.com/sqlserver/
• http://www.sqlmag.com 中有以期刊的形式提供信息的外部资源。您可以在其中找到许多优化和调整提示、代码示例、概述 SQL Server 内部工作原理的见解深刻的文章，以及其他有价值的信息。
• Delaney、Kalen 和 Soukup, Ron 合著。Inside Microsoft SQL Server 2000（Microsoft SQL Server 2000 技术内幕），Microsoft Press，2001

  该书是对上一版本（Inside Microsoft SQL Server 7.0 (Microsoft SQL Server 7.0 技术内幕)）的更新，其中纳入了 SQL Server 2000 的信息。该书深入探讨了 SQL Server 的许多内部概念。

• Kimball, Ralph 著。The Data Warehouse Lifecycle Toolkit（数据仓库生存期工具箱），John Wiley 和 Sons 合著，1998 年。

  该书深入探讨数据仓库的数据库设计，并解释了维度建模概念

文章来源于领测软件测试网 https://www.ltesting.net/