合 排查 IO 问题导致的SQL Server性能缓慢问题
Tags: MSSQLSQL Server性能整理自官网IO
- 定义 I/O 性能缓慢
- 方法
- 选项 1:通过 Azure Data Studio 直接在笔记本中执行步骤
- 选项 2:手动执行步骤
- 步骤 1:SQL Server报告 I/O 是否缓慢?
- I/O 等待类型
- sys.dm_io_virtual_file_stats 中的文件统计信息
- 错误日志或应用程序事件日志中报告的错误 833
- 步骤 2:Perfmon 计数器是否指示 I/O 延迟?
- 步骤 3:I/O 子系统是否超出容量?
- 步骤 4:SQL Server驱动繁重的 I/O 活动吗?
- 原因
- 方法的图形表示形式
- 有关 I/O 相关等待类型的信息
- PAGEIOLATCH_EX
- PAGEIOLATCH_SH
- PAGEIOLATCH_UP
- WRITELOG
- ASYNC_IO_COMPLETION
- IO_COMPLETION
- BACKUPIO
- 参考
定义 I/O 性能缓慢
性能监视器计数器用于确定 I/O 性能缓慢。 这些计数器测量 I/O 子系统在时钟时间方面平均处理每个 I/O 请求的速度。 测量 Windows 中 I/O 延迟的特定 性能监视器 计数器是 Avg Disk sec/ Read
、 Avg. Disk sec/Write
和 Avg. 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。
选项 2:手动执行步骤
以下步骤概述了该方法:
步骤 1:SQL Server报告 I/O 是否缓慢?
SQL Server可以通过多种方式报告 I/O 延迟: