检测和解决 SQL Server2000 SP4中问题

来自 Microsoft SQL Server Support 的实际示例

Microsoft SQL Server 和 Platforms Escalation Support 已经处理了下列方案，这些方案旨在提供一个参考框架，并且帮助树立有关延迟和阻塞 I/O 情况以及系统可能如何受到影响的预期。不存在给其他软硬件带来任何特殊或更高风险的特殊硬件或驱动程序集；在这个方面，所有系统都是相同的。

示例 1 — 阻塞 45 秒钟的日志写操作：

一个尝试性的 SQL Server 日志文件写操作周期性地阻塞 45 秒钟。该日志写操作无法及时完成，从而产生阻塞情况，导致 30 秒钟的客户端查询超时。

请求被提交并阻塞（日志写挂起），导致查询继续占用锁并且阻塞来自其他客户端的传入请求。其他客户端开始超时并且使问题变得复杂，这是因为应用程序没有被设计为在发生超时的时候回滚尚未解决的事务。这会导致数以百计尚未解决的事务占用锁以及严重的阻塞。（有关事务处理和阻塞的详细信息，请参阅 INF: Understanding and Resolving SQLServer 7.0 or 2000 Blocking Problems）。应用程序使用连接池来维护 Web 站点，因此，随着更多的连接被阻塞，Web 站点创建了更多的连接，而这些连接又会被阻塞，该循环会一直持续下去。

在大约 45 秒钟之后，该日志写操作将完成，但到此时为止，数以百计的连接已经积累起来，从而导致阻塞问题，并使得 SQL Server 和应用程序需要花费几分钟的时间进行恢复。当与应用程序问题结合起来的时候，延迟 I/O 状况会对系统产生非常消极的影响。

解决办法：这归因于 HBA 驱动程序中的延迟 I/O 请求。计算机具有多个带有故障转移支持的 HBA 卡。故障转移超时值被配置为 45 秒。当一个 HBA 落后或者在 45 秒钟或更长时间内未与 SAN 通信时，该 I/O 请求被路由到第二个 HBA 进行处理，并且会很快完成。硬件产品的推荐故障转移设置为 5 秒钟，以便避免出现这样的延迟状况。

如果在 SQL Server 2000 SP4 中已经有了新的自动报告该状况的功能，那么我们在疑难解答过程中就可以很快知道，基本问题是由于 SQL Server 外部的问题而发生的阻塞或延迟 I/O 操作。事实上，我们花费了大量时间来解决一个在最初呈现为普通性能问题的问题。

示例 2 — 筛选器驱动程序干预：

许多防病毒软件和备份产品使用 I/O 筛选器驱动程序。这些筛选器驱动程序成为 I/O 请求栈的一部分，并且可以访问 IRP 请求。Microsoft 技术支持部门已经遇见过各种问题 — 从导致阻塞 I/O 的错误到筛选器驱动程序实现中的延迟状况。

其中，Microsoft SQL Server 技术支持部门遇到的一种情况是，涉及到用于备份处理（该过程能够备份在备份时处于打开状态的文件）的筛选器驱动程序。系统管理员错误地在文件备份选择中包括了 SQL Server 数据文件目录。当备份发生时，它试图收集备份开始时文件的一致镜像。在完成该操作时，它将延迟后续的 I/O 请求，使它们能够在软件处理它们时逐个完成。

当备份开始时，SQL Server 的性能会急剧下降，因为针对 SQL Server 的 I/O 被强迫逐个完成。使该问题变得更为复杂的是，单 I/O 逻辑的特点使得 I/O 通常无法异步执行，因此当 SQL Server 期望发送 I/O 请求并继续工作时，UMS 辅助进程却在 I/O 完成之前一直阻塞在读或写调用中。SQL Server 预读功能实际上被筛选器驱动程序的操作禁用了。而且，即使当备份完成时，筛选器驱动程序中的另一个程序错误仍然使单 I/O 行为保持不变。恢复 SQL Server 性能的唯一方法是关闭数据库并重新打开它或者重新启动 SQL Server，以便在当前筛选器驱动程序交互未就绪的情况下释放并重新获取文件句柄。

解决办法：将 SQL Server 的数据文件从文件备份过程中排除，并且解决筛选器驱动程序中的导致文件被置于单 I/O 模式的程序错误。

示例 3 — 隐藏的错误：

很多高端系统具有用于处理负载平衡的多通道 I/O 路径以及类似的工具。Microsoft SQL Server 技术支持部门已经见过使用此类软件的情况，其中，尽管 I/O 请求失败，但软件确实正确地处理了错误状况，并且执行了无数次重试。I/O 被阻塞，并且 SQL Server 无法完成指定的操作。与上面描述的日志写状况非常类似，在这样的状况对系统产生了消极影响之后，发生了很多糟糕的系统行为。

解决办法：在类似情况下，重新启动 SQL Server 可以在一定程度上缓解问题，但是，有时需要重新启动 Windows 来使处理恢复到正常状态。当然，I/O 子系统中的程序错误最终需要由 I/O 供应商解决。

SQL Server 2000 SP4 的新的对此类状况进行自动报告的功能使得类似问题的检测变得更加容易。我们不仅可以看到整个服务器的总体性能下降，而且还可以通过 SP4 所记录的新消息洞察问题的本质，并且知道该问题很可能出在 SQL Server 外部。

示例 4 — 远程存储/镜像/RAID 驱动器：

很多系统使用镜像或类似的技术来帮助防止丢失数据。其中一些系统是基于软件的，而其他系统是基于硬件的。Microsoft SQL Server 技术支持部门经常遇到的与这些系统有关的情况是延迟增加。

当针对镜像的 I/O 必须在 I/O 操作被视为完成之前成功完成时，这显然会增加总体 I/O 时间。对于远程镜像安装，网络延迟和重试可能成为一个不利因素。当发生驱动器故障并且 RAID 子系统重新生成时，I/O 吞吐量可能会受到影响。

解决办法：在类似情况下，我们通常建议使用严格的配置设置（这随供应商和设备而异），以减少镜像延迟和 RAID 重新生成操作。

RAID 系统开销和延迟可能导致 I/O 变慢，而 SQL Server 对此无能为力。就像任何其他应用程序一样，它是 RAID 硬件和驱动程序的客户端。当该类型的问题使服务器的速度过度降低时，SP4 中新的延迟和阻塞 I/O 报告功能有助于查明问题所在。

示例 5 — 压缩：

Microsoft 不在压缩驱动器上支持 SQL Server 7.0 或 2000 数据和日志文件。NTFS 压缩是不安全的，这不仅是因为它破坏了预写日志 (WAL) 协议，而且还因为它要求对每个 I/O 请求执行更多的处理。压缩禁止了异步 I/O，从而导致所有带有受影响数据或日志文件的 SQL Server I/O 都被同步执行。

解决办法：在这种情况下，我们总是建议客户解压缩他们的数据和日志文件。

NTFS 压缩可能导致 I/O 变慢，而 SQL Server 对此无能为力。就像任何其他用户模式应用程序一样，它是文件系统的客户端。当压缩对 SQL Server I/O 操作产生不利影响时，SP4 中新的延迟和阻塞 I/O 报告功能有助于查明问题所在。

附加数据点

系统进程中提供的等待类型信息可能有助于诊断 I/O 瓶颈。缓冲区 I/O 锁存器等待类型和写日志等待是调查 I/O 路径性能的关键指标。Microsoft 知识库文章 822101: The waittype and lastwaittype fields in the sysprocesses table 概述了等待类型，并且详细介绍了与诊断延迟或阻塞 I/O 状况有关的 I/O 等待类型。

小结

尽管阻塞和延迟 I/O 问题在 SQL Server 部署中很罕见，但从历史上来看，这些问题一旦发生，就非常难以解决。因为此类问题的根源通常存在于驱动程序或硬件设备中，所以调查和解决这类问题可能花费大量的时间，并且需要具有超出典型数据库管理员能力范围的专业技能。使用 SQL Server 2000 SP4 中的新工具可以显著减少解决此类问题所需的时间，并且最起码可以为 DBA 指明正确的方向。