教你轻松解决SQLServer2000SP4的问题

像SQL Server这样的数据库管理系统依赖于文件的及时输入/输出。故障或配置不当的硬件、固件设置、筛选器驱动程序、压缩、程序错误以及I/O路径中的其他情况都会导致I/O问题的阻塞或延迟，并且很快会对SQL Server性能产生负面影响。

上述问题对SQL Server的影响因问题的细节而异，但它们通常会导致阻塞、闩锁争用和超时、过长的响应时间和资源的过度利用。

阻塞的I/O指的是必须由外部干预完成的I/O请求(通常是I/O请求包(IRP))。这种情况通常需要完全重新启动系统或类似的操作来解决，并强烈表明I/O路径组件中存在硬件故障或程序错误。

延迟I/O指的是无需干预即可完成的I/O请求，但花费的时间比预期的长(同样，这通常是IRP)。这种情况通常是由于硬件配置、固件设置或过滤驱动干预造成的，需要硬件或软件厂商的帮助来跟踪解决。

SQL Server 2000 SP4包含数据库和日志文件I/O(读写)逻辑，用于检测延迟和阻塞情况。如果I/O操作在15秒或更长时间后仍未完成，SQL Server将检测并报告这种情况。SQL Server错误日志中将记录以下消息:

SQL Serverhas在数据库[stressdb] (7)中的文件[E:\SEDATA\stressdb5.ndf]上遇到了192
次IO请求，这些请求花费的时间超过了15秒。OS
文件句柄是0x00000000000074D4。最新长IO的偏移量为:
x 0000000022000”。

此消息表明当前工作负载需求超过了I/O路径或当前系统配置和功能，或者I/O路径包含无法正常工作的软件(固件、驱动程序)或硬件组件。

记录的错误消息提供以下信息:

•###出现次数—15秒内未能完成读取或写入操作的I/O请求的数量。

•文件—受影响文件的完整文件名、数据库名和DBID。

•文件—文件的操作系统句柄。调试器和其他实用程序可以使用这些信息来跟踪IRP请求。

•offset—上一次被阻塞或延迟的I/O的偏移量。调试器和其他实用程序可以使用此信息来跟踪IRP请求。(注意:在记录此消息时，此I/O可能不再被阻塞或延迟。)

记录和报告

根据文件进行I/O报告和记录。延迟和阻止I/O请求的检测和报告是两种不同的操作。

检测(日志记录)在SQL Server内部的两个地方处理。第一个位置是I/O实际完成的时间。如果请求时间超过15秒，则会进行录制操作。第二个位置是当编写器进程延迟时。当延迟编写器执行时，它包含一个新操作来检查所有挂起的数据和日志文件I/O请求，如果超过了15秒的阈值，将发生记录操作。

报告的执行间隔不少于5分钟。当对文件发出下一个I/O请求时，会发生报告操作。如果记录操作已经发生，并且自上次报告发生以来已经过了5分钟或更长时间，则向错误日志中写入新的报告(如上所示的错误消息)。

15秒的阈值目前不可调整。虽然不建议这样做，但是您可以使用跟踪标志830完全禁用延迟和阻塞的I/O检测。在SQL Server启动期间设置启动参数-- T830可以禁用延迟/阻塞I/O检测。使用dbcc traceon(830，-1)禁用对当前运行的SQL Server实例的检测。只有重新启动SQL Server，Dbcc traceon才会生效。

注意:延迟或阻塞的给定I/O请求只会报告一次。如果消息报告10个I/O被延迟，这10个报告将不会再次发生。如果下一条消息报告15个I/O被阻塞，这意味着15个新的I/O请求在性能和计划操作方面被延迟了。

整体系统性能可能在I/O处理中起着关键作用。在研究I/O延迟或阻塞的报告时，应考虑系统的综合运行状态。过度的负载可能会降低整个系统的速度(包括I/O处理)。问题发生时系统的行为可能是确定问题根源的关键。例如，如果出现问题时CPU利用率变高或保持在高水平，这可能表明系统中的某个进程消耗了太多的CPU时间，以至于以各种方式对其他进程产生了负面影响。

检查性能计数器“平均磁盘秒/传输”和“平均磁盘队列长度”或“当前磁盘队列长度”以获取特定的I/O路径信息。例如，SQL Server计算机上的平均每秒磁盘传输时间通常不到15毫秒。如果该值上升，可能表明I/O子系统无法满足I/O要求。

请记住，SQL Server充分利用了Windows的异步I/O功能，并大幅扩展了磁盘队列长度，因此上述性能计数器的高值本身并不表示有问题。

索引和并行

一个很常见的情况是，大量I/O突发是因为索引丢失和扫描、哈希和排序对I/O系统造成的压力。运行一次“索引翻转向导”通常有助于解决系统的I/O压力。如果添加索引可以帮助查询避免表扫描，甚至避免排序或散列，则系统可以获得几个优点:

•减少完成操作所需的物理I/O直接等同于提高查询的性能。

•只有缓存中的一些页面需要翻转，因此缓存中的这些页面总是与活动查询相关。

•避免不必要的排序和散列。

•可以降低tempdb的利用率，减少争用情况。

•减少资源利用和/或并行操作。因为SQL Server不能保证服务器在决定是否对查询进行并行处理时会考虑并行查询的执行和系统中的负载，所以您会针对串行执行优化所有查询。在Q/A环境中，最大并行度应该设置为1，以便针对根本没有从服务器接收到并行计划的最坏情况进行调整。如果在测试环境中证明可以在串行模式下高效地执行查询，那么生产环境中的并行计划可以提供意想不到的性能改进。但是，在许多情况下，SQL Server选择并行执行，因为要遍历的绝对数据量太大。这些数据通常会直接受到索引的影响。例如，如果索引丢失，可能会发生大量排序操作。很容易看出多个辅助进程执行排序操作是如何使响应速度比串行排序更快的，但是我们需要知道，这个操作可能会大大增加I/O系统的压力。当多个工作进程并发运行时，来自多个工作进程的大量读取请求可能会导致I/O突发并增加CPU利用率。很多时候，如果添加了索引或发生了其他调整操作，您可以调整查询以使其运行得更快并使用更少的资源。这不仅提高了相关查询的性能，也提高了系统的整体性能。
来自Microsoft SQL Server支持部门的实际示例
Microsoft SQL Server和平台升级支持部门已经处理了以下方案，这些方案旨在提供一个参考框架，并帮助建立对延迟和阻塞I/O情况以及系统可能受到的影响的预期。不存在给其他硬件和软件带来任何特殊或更高风险的特殊硬件或驱动程序集；在这方面，所有系统都是一样的。

示例1—45秒的块日志写操作:

尝试性的SQL Server日志文件写入操作会定期被阻止45秒。日志写操作无法及时完成，导致阻塞情况，导致客户端查询超时30秒。

请求被提交并被阻塞(日志写入被挂起)，这导致查询继续占用锁并阻塞来自其他客户端的传入请求。其他客户端开始超时并使问题变得复杂，因为应用程序并不是为在超时发生时回滚未解决的事务而设计的。这将导致数百个未解决的事务占用锁和严重的拥塞。(有关事务处理和阻塞的详细信息，请参阅INF:了解和解决SQL Server 7.0或2000阻塞问题)。应用程序使用连接池来维护网站，因此随着更多的连接被阻塞，网站创建更多的连接，这些连接将被阻塞，循环将继续。

大约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请求。微软的技术支持部门遇到了各种各样的问题——从导致阻塞I/O的错误到过滤器驱动程序实现的延迟。

其中，微软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驱动器:

许多系统使用镜像或类似技术来帮助防止数据丢失。这些系统中的一些是基于软件的，而另一些是基于硬件的。微软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:sysprocesses表中的wait type和lastwaittype字段概述了等待类型，并详细说明了与诊断延迟或阻塞的I/O条件相关的I/O等待类型