合 排查 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 延迟：
- I/O 等待类型
- 视图
sys.dm_io_virtual_file_stats - 错误日志或应用程序事件日志
I/O 等待类型
确定SQL Server等待类型是否报告了 I/O 延迟。 其他几个不太常见的等待类型的值 PAGEIOLATCH_*、 WRITELOG和 ASYNC_IO_COMPLETION 值通常应保持在每个 I/O 请求的 10-15 毫秒以下。 如果这些值一致较大，则存在 I/O 性能问题，需要进一步调查。 以下查询可以帮助你收集系统上的此诊断信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | #replace with server\instance or server for default instance $sqlserver_instance = "server\instance" for ([int]$i = 0; $i -lt 100; $i++) { sqlcmd -E -S $sqlserver_instance -Q "SELECT r.session_id, r.wait_type, r.wait_time as wait_time_ms` FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s ` ON r.session_id = s.session_id ` WHERE wait_type in ('PAGEIOLATCH_SH', 'PAGEIOLATCH_EX', 'WRITELOG', ` 'IO_COMPLETION', 'ASYNC_IO_COMPLETION', 'BACKUPIO')` AND is_user_process = 1" Start-Sleep -s 2 } |
sys.dm_io_virtual_file_stats 中的文件统计信息
若要查看SQL Server中所述的数据库文件级延迟，请运行以下查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | #replace with server\instance or server for default instance $sqlserver_instance = "server\instance" sqlcmd -E -S $sqlserver_instance -Q "SELECT LEFT(mf.physical_name,100), ` ReadLatency = CASE WHEN num_of_reads = 0 THEN 0 ELSE (io_stall_read_ms / num_of_reads) END, ` WriteLatency = CASE WHEN num_of_writes = 0 THEN 0 ELSE (io_stall_write_ms / num_of_writes) END, ` AvgLatency = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ` ELSE (io_stall / (num_of_reads + num_of_writes)) END,` LatencyAssessment = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 'No data' ELSE ` CASE WHEN (io_stall / (num_of_reads + num_of_writes)) < 2 THEN 'Excellent' ` WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 2 AND 5 THEN 'Very good' ` WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 6 AND 15 THEN 'Good' ` WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 16 AND 100 THEN 'Poor' ` WHEN (io_stall / (num_of_reads + num_of_writes)) BETWEEN 100 AND 500 THEN 'Bad' ` ELSE 'Deplorable' END END, ` [Avg KBs/Transfer] = CASE WHEN (num_of_reads = 0 AND num_of_writes = 0) THEN 0 ` ELSE ((([num_of_bytes_read] + [num_of_bytes_written]) / (num_of_reads + num_of_writes)) / 1024) END, ` LEFT (mf.physical_name, 2) AS Volume, ` LEFT(DB_NAME (vfs.database_id),32) AS [Database Name]` FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs ` JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id ` AND vfs.file_id = mf.file_id ` ORDER BY AvgLatency DESC" |
查看 AvgLatency 和 LatencyAssessment 列以了解延迟详细信息。
错误日志或应用程序事件日志中报告的错误 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错误日志：
1 2 | Get-ChildItem -Path "c:\program files\microsoft sql server\mssql*" -Recurse -Include Errorlog | Select-String "occurrence(s) of I/O requests taking longer than Longer than 15 secs" |
此外，有关此错误的详细信息，请参阅 MSSQLSERVER_833 部分。
步骤 2：Perfmon 计数器是否指示 I/O 延迟？
如果SQL Server报告 I/O 延迟，请参阅 OS 计数器。 可以通过检查延迟计数器 Avg Disk Sec/Transfer来确定是否存在 I/O 问题。 以下代码片段指示通过 PowerShell 收集此信息的一种方法。 它会收集所有磁盘卷上的计数器：“_total”。 更改为特定驱动器卷 (，例如“D：”) 。 若要查找托管数据库文件的卷，请在SQL Server中运行以下查询：
1 2 3 4 5 | #replace with server\instance or server for default instance $sqlserver_instance = "server\instance" sqlcmd -E -S $sqlserver_instance -Q "SELECT DISTINCT LEFT(volume_mount_point, 32) AS volume_mount_point ` FROM sys.master_files f ` CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs" |
收集 Avg Disk Sec/Transfer 所选卷上的指标：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | clear $cntr = 0 # replace with your server name, unless local computer $serverName = $env:COMPUTERNAME # replace with your volume name - C: , D:, etc $volumeName = "_total" $Counters = @(("\\$serverName" +"\LogicalDisk($volumeName)\Avg. disk sec/transfer")) $disksectransfer = Get-Counter -Counter $Counters -MaxSamples 1 $avg = $($disksectransfer.CounterSamples | Select-Object CookedValue).CookedValue Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 30 | ForEach-Object { $_.CounterSamples | ForEach-Object { [pscustomobject]@{ TimeStamp = $_.TimeStamp Path = $_.Path Value = ([Math]::Round($_.CookedValue, 5)) turn = $cntr = $cntr +1 running_avg = [Math]::Round(($avg = (($_.CookedValue + $avg) / 2)), 5) } | Format-Table } } write-host "Final_Running_Average: $([Math]::Round( $avg, 5)) sec/transfer`n" if ($avg -gt 0.01) { Write-Host "There ARE indications of slow I/O performance on your system" } else { Write-Host "There is NO indication of slow I/O performance on your system" } |
如果此计数器的值始终高于 10-15 毫秒，则需要进一步了解问题。 在大多数情况下，偶尔出现峰值不计算在内，但请务必仔细检查峰值的持续时间。 如果峰值持续了一分钟或更多，则它更代表一个高原，而不是峰值。
