sqlserversp4:教你轻松解决SQL Server 2000 SP4的问题来源: 发布时间:星期二, 2009年1月13日 浏览:6次 评论:0
="t18">像 SQL Server 这样
 数据库管理系统依赖于文件输入/输出操作及时进行 有故障或配置不当硬件、固件设置、筛选器驱动 、压缩、 以及 I/O 路径内其他情况都可能导致阻塞或延迟 I/O 问题 并且很快对 SQL Server 性能产生消极影响 上述问题对 SQL Server 影响因问题细节区别而差异很大但它们通常导致阻塞、锁存器争用和超时、过长响应时间以及资源过度利用 阻塞 I/O 是指必须进行外部干预才能完成 I/O 请求(通常是 I/O 请求包 (IRP))这种状况通常需要执行完整系统重新启动或类似操作才能解决并且强烈表明硬件有故障或者在 I/O 路径组件中存在 延迟 I/O 是指无需干预即可完成但所花时间超过预期时间 I/O 请求(同样这通常是 IRP)这种状况原因通常是硬件配置、固件设置或筛选器驱动干预需要硬件或软件Software供应商提供帮助以便跟踪和解决 SQL Server 2000 SP4 包含数据库和日志文件 I/O(读和写)逻辑以便检测延迟和阻塞状况 当 I/O 操作经过 15 秒钟或更长时间仍未完成时SQL Server 会检测到并报告这 状况以下消息将被记录到 SQL Server 日志中: 2007-11-1 00:21:25.26 spid1 SQL Serverhas encountered 192 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [E:\SEDATA\stressdb5.ndf] in database [stressdb] (7). The OS file handle is 0x00000000000074D4. The off  of the latest long IO is: x00000000022000". 该消息表明 当前工作负载需求超出了 I/O 路径或当前系统配置和功能或者 I/O 路径含有不能正常工作软件Software(固件、驱动)或硬件组件 所记录 信息提供了以下信息: • ### occurrences — 未能在 15 秒钟以内完成读或写操作 I/O 请求数量 • File information — 完整 文件名、数据库名和受影响文件 DBID • File handle — 该文件 操作系统句柄可以通过调试器和其他实用工具来使用这信息跟踪 IRP 请求 • Off — 上个阻塞或延迟 I/O 偏移量可以通过调试器和其他实用工具来使用这信息跟踪 IRP 请求(注:在记录该消息时候该 I/O 可能不再阻塞或延迟) 记录和报告 I/O 报告和记录是按照文件执行延迟和阻塞 I/O 请求检测和报告是两个区别操作 检测(记录)是在 SQL Server 内部 两个位置处理第个位置是在 I/O 实际完成时候如果请求花费了 15 秒钟以上则发生记录操作第 2个位置是在延迟写入器进程执行时候当延迟写入器执行时它包含新对所有挂起数据和日志文件 I/O 请求进行检查操作并且如果已经超过了 15 秒钟阈值则会发生记录操作 报告是按照不低于 5 分钟 时间间隔执行当对文件进行下次 I/O 请求时发生报告操作如果记录操作已经发生并且自上次报告发生以来已经过去了 5 分钟或更长时间则向日志中写入新报告(上面显示消息)15 秒钟 阈值当前是不可调整尽管不推荐这样做但您可以用跟踪标志 830 完全禁用延迟和阻塞 I/O 检测在 SQL Server 启动期间设置启动参数 –T830 可以禁用延迟/阻塞 I/O 检测使用 dbcc traceon(830, -1) 可以禁用对当前正在运行 SQL Server 例子检测只有重新启动 SQL ServerDbcc traceon 才会生效 注:延迟或阻塞 给定 I/O 请求只会报告次如果消息报告 10 个 I/O 被延迟则这 10 个报告不会再次发生如果下个消息报告 15 个 I/O 被阻塞则表明 15 个新 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 通常低于 15ms如果该值上升则可能表明 I/O 子系统无法满足 I/O 要求 请记住 SQL Server 充分利用了 Windows 异步 I/O 功能并且猛烈地扩展磁盘队列长度因此上述性能计数器具有较高值本身并不表明存在问题 索引和并行性 特别常见 种情况是 索引丢失以及由此导致扫描、哈希和排序对 I/O 系统造成压力所以突发大量 I/O运行遍“Index Turning Wizard”通常会有助于解决系统 I/O 压力如果添加索引可以帮助查询避免表扫描甚至排序或哈希则系统可以获得多个优点: • 减少完成操作所需 物理 I/O这直接等效于提高查询性能 • 数据缓存Cache中只有较少 页面必须周转因此缓存Cache中那些页面可以直和活动查询相关 • 避免不必要 排序和哈希 • 可以降低 tempdb 利用率和减少争用情况 • 减少资源利用率和/或并行操作 SQL Server 不能保证服务器在确定是否将查询并行化时考虑并行查询执行和系统中负载所以您最好针对串行执行优化所有查询在 Q/A 环境中应该将 max degree of parallelism 设置为 1以便对根本没有从服务器收到任何并行计划最糟糕情况强行进行调整如果在测试环境中证实查询可以按串行方式高效执行则生产环境中并行计划可以提供出乎意料性能改进但是很多情况下SQL Server 选择并行执行这是要遍历数据绝对数量过于庞大该数据量通常直接受到索引影响例如如果丢失索引则可能产生大量排序操作我们很容易就可以看出执行排序操作多个辅助进程如何使响应速度比以串行方式处理排序更快速不过我们需要了解该操作可能大幅增加 I/O 系统压力当多个辅助进程并发运行时来自多个辅助进程大型读请求可能导致 I/O 突发以及 CPU 利用率提高很多时候如果添加了索引或者发生了其他调整操作则可以调整查询以使其更快地运行并使用更少资源这不仅提高了相关查询性能而且还提高了系统整体性能来自 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 请求被路由到第 2个 HBA 进行处理并且会很快完成硬件产品推荐故障转移设置为 5 秒钟以便避免出现这样延迟状况 如果在 SQL Server 2000 SP4 中已经有了新 自动报告该状况功能那么我们在疑难解答过程中就可以很快知道基本问题是由于 SQL Server 外部问题而发生阻塞或延迟 I/O 操作事实上我们花费了大量时间来解决个在最初呈现为普通性能问题问题 举例 2 — 筛选器驱动 干预: 许多防病毒软件Software和备份产品使用 I/O 筛选器驱动 这些筛选器驱动成为 I/O 请求栈部分并且可以访问 IRP 请求Microsoft 技术支持部门已经遇见过各种问题 — 从导致阻塞 I/O 到筛选器驱动实现中延迟状况 其中 Microsoft SQL Server 技术支持部门遇到种情况是涉及到用于备份处理(该过程能够备份在备份时处于打开状态文件)筛选器驱动系统管理员地在文件备份选择中包括了 SQL Server 数据文件目录当备份发生时它试图收集备份开始时文件致镜像在完成该操作时它将延迟后续 I/O 请求使它们能够在软件Software处理它们时逐个完成 当备份开始时 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 技术支持部门已经见过使用此类软件Software情况其中尽管 I/O 请求失败但软件Software确实正确地处理了状况并且执行了无数次重试I/O 被阻塞并且 SQL Server 无法完成指定操作和上面描述日志写状况非常类似在这样状况对系统产生了消极影响的后发生了很多糟糕系统行为 解决办法:在类似情况下 重新启动 SQL Server 可以在定程度上缓解问题但是有时需要重新启动 Windows 来使处理恢复到正常状态当然I/O 子系统中最终需要由 I/O 供应商解决 SQL Server 2000 SP4 新对此类状况进行自动报告功能使得类似问题检测变得更加容易我们不仅可以看到整个服务器总体性能下降而且还可以通过 SP4 所记录新消息洞察问题本质并且知道该问题很可能出在 SQL Server 外部举例 4 — 远程存储/镜像/RAID 驱动器: 很多系统使用镜像或类似 技术来帮助防止丢失数据其中些系统是基于软件Software而其他系统是基于硬件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 等待类型0
相关文章
读者评论
发表评论 |
 |
专注于互联网--专注于架构 |
最新标签 网站地图 文章索引 Rss订阅 |
