摘要本文探讨了 SQL Server Service Pack 中的报告工具如何显着减少为识别和确定延迟和阻塞 I/O 操作的根源所花费的时间
简介
像 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 SP 包含数据库和日志文件 I/O（读和写）逻辑以便检测延迟和阻塞状况当 I/O 操作经过 秒钟或更长时间仍未完成时SQL Server 会检测到并报告这一状况以下消息将被记录到 SQL Server 错误日志中
:: spid SQL Serverhas encountered
occurrence(s) of IO requests taking longer than seconds to complete
on file [E:\SEDATA\stressdbndf] in database [stressdb] () The OS
file handle is xD The offset of the latest long IO is:
x
该消息表明当前工作负载需求超出了 I/O 路径或当前系统配置和功能或者 I/O 路径含有不能正常工作的软件（固件驱动程序）或硬件组件
所记录的错误信息提供了以下信息
### occurrences — 未能在 秒钟以内完成读或写操作的 I/O 请求的数量
File information — 完整的文件名数据库名和受影响文件的 DBID
File handle — 该文件的操作系统句柄可以通过调试器和其他实用工具来使用这一信息跟蹤 IRP 请求
Offset — 上一个阻塞或延迟 I/O 的偏移量可以通过调试器和其他实用工具来使用这一信息跟蹤 IRP 请求（注在记录该消息的时候该 I/O 可能不再阻塞或延迟）
记录与报告
I/O 的报告和记录是按照文件执行的延迟和阻塞 I/O 请求的检测和报告是两个不同的操作
检测（记录）是在 SQL Server 内部的两个位置处理的第一个位置是在 I/O 实际完成的时候如果请求花费了 秒钟以上则发生记录操作第二个位置是在延迟写入器进程执行的时候当延迟写入器执行时它包含新的对所有挂起的数据和日志文件 I/O 请求进行检查的操作并且如果已经超过了 秒钟的阈值则会发生记录操作
报告是按照不低于 分钟的时间间隔执行的当对文件进行下一次 I/O 请求时发生报告操作如果记录操作已经发生并且自上一次报告发生以来已经过去了 分钟或更长时间则向错误日志中写入新的报告（上面显示的错误消息）
秒钟的阈值当前是不可调整的尽管不推荐这样做但您可以用跟蹤标志 完全禁用延迟和阻塞 I/O 检测在 SQL Server 启动期间设置启动参数 –T 可以禁用延迟/阻塞 I/O 检测使用 dbcc traceon( ) 可以禁用对当前正在运行的 SQL Server 实例的检测只有重新启动 SQL ServerDbcc traceon 才会生效
注 延迟或阻塞的给定 I/O 请求只会报告一次如果消息报告 个 I/O 被延迟则这 个报告不会再次发生如果下一个消息报告 个 I/O 被阻塞则表明 个新的 I/O 请求已经被延迟
性能和计划操作
总体系统性能可能在 I/O 处理中扮演关键的角色在研究延迟或阻塞 I/O 的报告时应该考虑系统的综合运行状况过多的负载可能导致整个系统（包括 I/O 处理）变慢系统在发生问题时的行为可能是确定问题根源的关键所在例如如果 CPU 利用率在发生问题时变高或者保持较高水平则可能表明系统中的某个进程正在消耗如此之多的 CPU 时间以至于它以各种方式对其他进程产生了消极影响
请查看性能计数器 Average Disk Sec/Transfer 以及 Average Disk Queue Length 或 Current Disk Queue Length以获得特定的 I/O 路径信息例如SQL Server 计算机上的 Average Disk Sec/Transfer 通常低于 ms如果该值上升则可能表明 I/O 子系统无法满足 I/O 要求
请记住SQL Server 充分利用了 Windows 的异步 I/O 功能并且猛烈地扩展磁盘队列长度因此上述性能计数器具有较高的值本身并不表明存在问题
索引和并行性
特别常见的一种情况是因为索引丢失以及由此导致的扫描哈希和排序对 I/O 系统造成的压力所以突发大量的 I/O运行一遍Index Turning Wizard通常会有助于解决系统的 I/O 压力如果添加索引可以帮助查询避免表扫描甚至排序或哈希则系统可以获得多个优点
减少完成操作所需的物理 I/O这直接等效于提高查询的性能
数据缓存中只有较少的页面必须周转因此缓存中的那些页面可以一直与活动查询相关
避免不必要的排序和哈希
可以降低 tempdb 利用率和减少争用情况
减少资源利用率和/或并行操作因为 SQL Server 不能保证服务器在确定是否将查询并行化时考虑并行查询执行和系统中的负载所以您最好针对串行执行优化所有查询在 Q/A 环境中应该将 max degree of parallelism 设置为 以便对根本没有从服务器收到任何并行计划的最糟糕情况强行进行调整如果在测试环境中证实查询可以按串行方式高效执行则生产环境中的并行计划可以提供出乎意料的性能改进但是很多情况下SQL Server 选择并行执行这是因为要遍历数据的绝对数量过于庞大该数据量通常直接受到索引的影响例如如果丢失索引则可能产生大量排序操作我们很容易就可以看出执行排序操作的多个辅助进程如何使响应速度比以串行方式处理排序更快速不过我们需要了解该操作可能大幅增加 I/O 系统的压力当多个辅助进程并发运行时来自多个辅助进程的大型读请求可能导致 I/O 突发以及 CPU 利用率提高很多时候如果添加了索引或者发生了其他调整操作则可以调整查询以使其更快地运行并使用更少的资源这不仅提高了相关查询的性能而且还提高了系统的整体性能
来自 Microsoft SQL Server Support 的实际示例
Microsoft SQL Server 和 Platforms Escalation Support 已经处理了下列方案这些方案旨在提供一个参考框架并且帮助树立有关延迟和阻塞 I/O 情况以及系统可能如何受到影响的预期不存在给其他软硬件带来任何特殊或更高风险的特殊硬件或驱动程序集在这个方面所有系统都是相同的
示例 — 阻塞 秒钟的日志写操作
一个尝试性的 SQL Server 日志文件写操作周期性地阻塞 秒钟该日志写操作无法及时完成从而产生阻塞情况导致 秒钟的客户端查询超时
请求被提交并阻塞（日志写挂起）导致查询继续占用锁并且阻塞来自其他客户端的传入请求其他客户端开始超时并且使问题变得复杂这是因为应用程序没有被设计为在发生超时的时候回滚尚未解决的事务这会导致数以百计尚未解决的事务占用锁以及严重的阻塞应用程序使用连接池来维护 Web 站点因此随着更多的连接被阻塞Web 站点创建了更多的连接而这些连接又会被阻塞该循环会一直持续下去
在大约 秒钟之后该日志写操作将完成但到此时为止数以百计的连接已经积累起来从而导致阻塞问题并使得 SQL Server 和应用程序需要花费几分钟的时间进行恢复当与应用程序问题结合起来的时候延迟 I/O 状况会对系统产生非常消极的影响
解决办法这归因于 HBA 驱动程序中的延迟 I/O 请求计算机具有多个带有故障转移支持的 HBA 卡故障转移超时值被配置为 秒当一个 HBA 落后或者在 秒钟或更长时间内未与 SAN 通信时该 I/O 请求被路由到第二个 HBA 进行处理并且会很快完成硬件产品的推荐故障转移设置为 秒钟以便避免出现这样的延迟状况
如果在 SQL Server SP 中已经有了新的自动报告该状况的功能那么我们在疑难解答过程中就可以很快知道基本问题是由于 SQL Server 外部的问题而发生的阻塞或延迟 I/O 操作事实上我们花费了大量时间来解决一个在最初呈现为普通性能问题的问题
示例 — 筛选器驱动程序干预
许多防病毒软件和备份产品使用 I/O 筛选器驱动程序这些筛选器驱动程序成为 I/O 请求栈的一部分并且可以访问 IRP 请求Microsoft 技术支持部门已经遇见过各种问题 — 从导致阻塞 I/O 的错误到筛选器驱动程序实现中的延迟状况
其中Microsoft SQL Server 技术支持部门遇到的一种情况是涉及到用于备份处理（该过程能够备份在备份时处于打开状态的文件）的筛选器驱动程序系统管理员错误地在文件备份选择中包括了 SQL Server 数据文件目录当备份发生时它试图收集备份开始时文件的一致镜像在完成该操作时它将延迟后续的 I/O 请求使它们能
