合 排查 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 毫秒,则需要进一步了解问题。 在大多数情况下,偶尔出现峰值不计算在内,但请务必仔细检查峰值的持续时间。 如果峰值持续了一分钟或更多,则它更代表一个高原,而不是峰值。