排查 IO 问题导致的SQL Server性能缓慢问题

0    201    1

Tags:

👉 本文共约6307个字,系统预计阅读时间或需24分钟。

定义 I/O 性能缓慢

性能监视器计数器用于确定 I/O 性能缓慢。 这些计数器测量 I/O 子系统在时钟时间方面平均处理每个 I/O 请求的速度。 测量 Windows 中 I/O 延迟的特定 性能监视器 计数器是 Avg Disk sec/ ReadAvg. Disk sec/WriteAvg. Disk sec/Transfer (读取和写入) 累积。

在SQL Server中,事情的工作方式相同。 通常,查看SQL Server是否报告以时钟时间 (毫秒) 度量的任何 I/O 瓶颈。 SQL Server通过调用 Win32 函数(如 ReadFile()WriteFileGather()ReadFileScatter()WriteFile()向 OS 发出 I/O 请求。 发布 I/O 请求时,SQL Server次请求并使用等待类型报告请求的持续时间。

SQL Server使用等待类型来指示产品中不同位置的 I/O 等待。 与 I/O 相关的等待包括:

如果这些等待持续超过 10-15 毫秒,则 I/O 被视为瓶颈。

备注

为了提供上下文和视角,在故障排除SQL Server领域,Microsoft CSS 观察到 I/O 请求需要超过 1 秒且每次传输的 I/O 系统需要优化 15 秒的情况。 相反,Microsoft CSS 看到吞吐量低于 1 毫秒/传输的系统。 使用当今的 SSD/NVMe 技术,播发的吞吐量速率以每传输数十微秒为单位。 因此,10-15 毫秒/传输数字是我们根据 Windows 和 SQL Server 工程师多年来的集体经验选择的非常近似的阈值。 通常,当数字超过此大致阈值时,SQL Server用户开始看到其工作负载中的延迟并报告这些延迟。 最终,I/O 子系统的预期吞吐量由制造商、型号、配置、工作负载以及可能的其他多个因素定义。

方法

本文末尾的流程图介绍了 Microsoft CSS 用于处理SQL Server慢速 I/O 问题的方法。 它不是一种详尽或排他的方法,但已证明在隔离问题并解决问题方面非常有用。

可以选择以下两个选项之一来解决问题:

选项 1:通过 Azure Data Studio 直接在笔记本中执行步骤

备注

在尝试打开此笔记本之前,请确保在本地计算机上安装了 Azure Data Studio。 若要安装它,请转到 了解如何安装 Azure Data Studio

在 Azure Data Studio 中打开笔记本

选项 2:手动执行步骤

以下步骤概述了该方法:

步骤 1:SQL Server报告 I/O 是否缓慢?

SQL Server可以通过多种方式报告 I/O 延迟:

  • I/O 等待类型
  • 视图 sys.dm_io_virtual_file_stats
  • 错误日志或应用程序事件日志
I/O 等待类型

确定SQL Server等待类型是否报告了 I/O 延迟。 其他几个不太常见的等待类型的值 PAGEIOLATCH_*WRITELOGASYNC_IO_COMPLETION 值通常应保持在每个 I/O 请求的 10-15 毫秒以下。 如果这些值一致较大,则存在 I/O 性能问题,需要进一步调查。 以下查询可以帮助你收集系统上的此诊断信息:

sys.dm_io_virtual_file_stats 中的文件统计信息

若要查看SQL Server中所述的数据库文件级延迟,请运行以下查询:

查看 AvgLatencyLatencyAssessment 列以了解延迟详细信息。

错误日志或应用程序事件日志中报告的错误 833

在某些情况下,可能会在错误日志中观察到错误 833 SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d) 。 可以通过运行以下 PowerShell 命令来检查系统上SQL Server错误日志:

此外,有关此错误的详细信息,请参阅 MSSQLSERVER_833 部分。

步骤 2:Perfmon 计数器是否指示 I/O 延迟?

如果SQL Server报告 I/O 延迟,请参阅 OS 计数器。 可以通过检查延迟计数器 Avg Disk Sec/Transfer来确定是否存在 I/O 问题。 以下代码片段指示通过 PowerShell 收集此信息的一种方法。 它会收集所有磁盘卷上的计数器:“_total”。 更改为特定驱动器卷 (,例如“D:”) 。 若要查找托管数据库文件的卷,请在SQL Server中运行以下查询:

收集 Avg Disk Sec/Transfer 所选卷上的指标:

如果此计数器的值始终高于 10-15 毫秒,则需要进一步了解问题。 在大多数情况下,偶尔出现峰值不计算在内,但请务必仔细检查峰值的持续时间。 如果峰值持续了一分钟或更多,则它更代表一个高原,而不是峰值。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!
AiDBA后续精彩内容已被站长无情隐藏,请输入验证码解锁本文!
验证码:
获取验证码: 请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复