排查 I/O 问题导致 SQL Server 性能缓慢的问题

项目
06/19/2024

适用于： SQL Server

本文提供有关哪些 I/O 问题会导致 SQL Server 性能降低以及如何排查这些问题的指导。

定义 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 函数（例如 WriteFile()、、 ReadFile()WriteFileGather()和 ReadFileScatter()）向 OS 发出 I/O 请求。当它发布 I/O 请求时，SQL Server 对请求进行超时，并使用等待类型报告请求的持续时间。 SQL Server 使用等待类型来指示产品中不同位置的 I/O 等待。与 I/O 相关的等待包括：

/ PAGEIOLATCH_SHPAGEIOLATCH_EX
WRITELOG
IO_COMPLETION
ASYNC_IO_COMPLETION
BACKUPIO

如果这些等待持续超过 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 子系统的预期吞吐量由制造商、型号、配置、工作负载以及可能的其他多个因素定义。

方法论

本文末尾的流程图介绍了 CSS Microsoft处理 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_*、 WRITELOG和 ASYNC_IO_COMPLETION 值通常应保持在每个 I/O 请求的 10-15 毫秒以下。如果这些值一致较大，则存在 I/O 性能问题，需要进一步调查。以下查询可以帮助你收集系统上的此诊断信息：

#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 中报告的数据库文件级延迟，请运行以下查询：

#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 错误日志：

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 中运行以下查询：

#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 所选卷上的指标：

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

如果性能监视器计数器不报告延迟，但 SQL Server 报告延迟，则问题出在 SQL Server 和分区管理器之间，即筛选器驱动程序。分区管理器是 OS 收集 Perfmon 计数器的 I/O 层。若要解决延迟问题，请确保正确排除筛选器驱动程序并解决筛选器驱动程序问题。防病毒软件、备份解决方案、加密、压缩等程序使用筛选器驱动程序。可以使用此命令列出系统上的筛选器驱动程序及其附加到的卷。然后，可以在分配的筛选器高度一文中查找驱动程序名称和软件供应商。

fltmc instances

有关详细信息，请参阅如何选择要在运行 SQL Server 的计算机上运行的防病毒软件。

避免使用加密文件系统 (EFS) 和文件系统压缩，因为它们会导致异步 I/O 同步，因此速度变慢。有关详细信息，请参阅异步磁盘 I/O 在 Windows 上显示为同步一文。

步骤 3：I/O 子系统是否超出容量？

如果 SQL Server 和 OS 指示 I/O 子系统速度缓慢，请检查原因是系统超出容量。可以通过查看 I/O 计数器 Disk Bytes/Sec、 Disk Read Bytes/Sec或 Disk Write Bytes/Sec来检查容量。请务必与系统管理员或硬件供应商联系，了解 SAN (或其他 I/O 子系统) 的预期吞吐量规格。例如，可以通过 SAN 交换机上的 2 GB/秒 HBA 卡或 2 GB/秒的专用端口推送不超过 200 MB/秒的 I/O。硬件制造商定义的预期吞吐量容量定义了从此处开始的方式。

clear

$serverName = $env:COMPUTERNAME
$Counters = @(
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Bytes/sec"),
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Read Bytes/sec"),
   ("\\$serverName" +"\PhysicalDisk(*)\Disk Write Bytes/sec")
   )
Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 20 | ForEach-Object  {
$_.CounterSamples | ForEach-Object       {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 3)) }
    }
 }

步骤 4：SQL Server 是否驱动繁重的 I/O 活动？

如果 I/O 子系统超出容量，请通过查看 Buffer Manager: Page Reads/Sec (最常见的罪魁祸首) 来找出 SQL Server 是否是罪魁祸首，并 Page Writes/Sec (特定实例不太常见的) 。如果 SQL Server 是主要的 I/O 驱动程序，而 I/O 卷超出了系统所能处理的范围，请与应用程序开发团队或应用程序供应商合作，以：

优化查询，例如：更好的索引、更新统计信息、重写查询和重新设计数据库。
增加最大服务器内存或在系统上添加更多 RAM。更多的 RAM 将缓存更多的数据或索引页，而无需频繁从磁盘中重新读取，这将减少 I/O 活动。增加的内存也会减少 Lazy Writes/sec，这在经常需要将更多数据库页存储在有限的可用内存中时，延迟编写器刷新会驱动。
如果发现页面写入是大量 I/O 活动的来源，请检查 Buffer Manager: Checkpoint pages/sec 是否是由于满足恢复间隔配置要求而需要进行大量页面刷新。可以使用间接检查点随时间推移均衡 I/O，或增加硬件 I/O 吞吐量。

原因

通常，以下问题是 SQL Server 查询出现 I/O 延迟的高级原因：

硬件问题：
- SAN 配置错误 (交换机、电缆、HBA、存储)
- 超出的 I/O 容量 (整个 SAN 网络（而不仅仅是后端存储) ）
- 驱动程序或固件问题
硬件供应商和/或系统管理员需要在此阶段参与。
查询问题： SQL Server 正在使具有 I/O 请求的磁盘卷饱和，并且正在将 I/O 子系统推送到容量之外，从而导致 I/O 传输速率过高。在这种情况下，解决方案是查找导致大量逻辑读取 (或写入) 的查询，并优化这些查询，以最大程度地减少磁盘 I/O，使用适当的索引是执行此操作的第一步。此外，请保持统计信息的更新，因为它们为查询优化器提供足够的信息来选择最佳计划。此外，不正确的数据库设计和查询设计可能会导致 I/O 问题增加。因此，重新设计查询和有时表可能有助于改进 I/O。
筛选器驱动程序： 如果文件系统筛选器驱动程序处理大量 I/O 流量，SQL Server I/O 响应可能会受到严重影响。建议软件供应商对防病毒扫描进行适当的文件排除和正确的筛选器驱动程序设计，以防止对 I/O 性能造成影响。
其他应用程序 () ： 同一台具有 SQL Server 的计算机上的另一个应用程序可能会通过过多的读取或写入请求使 I/O 路径饱和。这种情况可能会使 I/O 子系统超出容量限制，并导致 SQL Server 的 I/O 速度变慢。标识应用程序并对其进行优化或将其移动到其他位置，以消除其对 I/O 堆栈的影响。

方法的图形表示形式

用于更正 SQL Server 缓慢 I/O 问题的方法的直观表示形式。

下面是报告磁盘 I/O 问题时 SQL Server 中观察到的常见等待类型的说明。

PAGEIOLATCH_EX

当任务在 I/O 请求中等待数据或索引页的闩锁 (缓冲区) 时发生。闩锁请求处于独占模式。当缓冲区写入磁盘时，将使用独占模式。长时间等待可能表示磁盘子系统存在问题。

PAGEIOLATCH_SH

当任务在 I/O 请求中等待数据或索引页的闩锁 (缓冲区) 时发生。闩锁请求处于共享模式。从磁盘读取缓冲区时，将使用共享模式。长时间等待可能表示磁盘子系统存在问题。

PAGEIOLATCH_UP

当任务在 I/O 请求中等待缓冲区的闩锁时发生。闩锁请求处于更新模式。长时间等待可能表示磁盘子系统存在问题。

WRITELOG

在任务等待事务日志刷新完成时发生。当日志管理器将其临时内容写入磁盘时，会发生刷新。导致日志刷新的常见操作是事务提交和检查点。

长时间等待的 WRITELOG 常见原因包括：

事务日志磁盘延迟：这是最常见的等待原因 WRITELOG 。通常，建议将数据和日志文件保存在单独的卷上。事务日志写入是连续写入，而从数据文件读取或写入数据是随机的。将数据和日志文件混合在一个驱动器卷上 (尤其是传统的旋转磁盘驱动器) 将导致磁盘头过度移动。
VLF 过多：) VLF (虚拟日志文件过多，可能会导致 WRITELOG 等待。过多的 VLF 可能会导致其他类型的问题，例如长时间恢复。
过多的小事务：虽然大型事务可能会导致阻塞，但过多的小事务可能会导致另一组问题。如果不显式开始事务，则任何插入、删除或更新都将导致事务 (我们调用此自动事务) 。如果在一个循环中执行 1,000 次插入，则会生成 1,000 个事务。此示例中的每个事务都需要提交，这会导致事务日志刷新和 1,000 个事务刷新。如果可能，请将单个更新、删除或插入到更大的事务中，以减少事务日志刷新并提高性能。此操作可以减少 WRITELOG 等待时间。
计划问题会导致日志编写器线程无法以足够快的速度进行计划：在 SQL Server 2016 之前，单个日志编写器线程执行所有日志写入。如果线程计划 (例如 CPU) 过高时出现问题，则日志编写器线程和日志刷新都可能会延迟。在 SQL Server 2016 中，最多添加了四个日志编写器线程，以增加日志写入吞吐量。请参阅 SQL 2016 - 它只是运行得更快：多个日志编写器辅助角色。在 SQL Server 2019 中，最多添加了 8 个日志编写器线程，从而进一步提高了吞吐量。此外，在 SQL Server 2019 中，每个常规工作线程都可以直接执行日志写入，而不是发布到日志编写器线程。通过这些改进， WRITELOG 计划问题很少触发等待。

ASYNC_IO_COMPLETION

发生以下某些 I/O 活动时发生：

大容量插入提供程序 (“插入批量”) 在执行 I/O 时使用此等待类型。
读取 LogShipping 中的撤消文件，并定向异步 I/O 进行日志传送。
在数据备份期间从数据文件读取实际数据。

IO_COMPLETION

在等待 I/O 操作完成时发生。此等待类型通常涉及与数据页无关的 I/O， (缓冲区) 。示例包括：

在溢出期间从/向磁盘读取和写入排序/哈希结果 (检查 tempdb 存储) 的性能。
将预先的假脱机读取和写入磁盘 (检查 tempdb 存储) 。
在导致从磁盘读取日志的任何操作（例如，恢复) ）期间， (从事务日志读取日志块。
尚未设置数据库时，从磁盘读取页面。
将页面复制到数据库快照 (写入时复制) 。
关闭数据库文件和文件解压缩。

通过

排查 I/O 问题导致 SQL Server 性能缓慢的问题

定义 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 活动？

原因

方法的图形表示形式

PAGEIOLATCH_EX

PAGEIOLATCH_SH

PAGEIOLATCH_UP

WRITELOG

ASYNC_IO_COMPLETION

IO_COMPLETION

BACKUPIO

反馈

其他资源

通过

排查 I/O 问题导致 SQL Server 性能缓慢的问题

定义 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

反馈

其他资源