排查 I/O 问题导致 SQL Server 性能缓慢的问题 |
您所在的位置：网站首页 › 磁盘的读取和写入是什么 › 排查 I/O 问题导致 SQL Server 性能缓慢的问题 |
| 排查 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 堆栈的影响。 方法的图形表示形式有关 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 存储) 。 在导致从磁盘读取日志的任何操作（例如，恢复) ）期间， (从事务日志读取日志块。 尚未设置数据库时，从磁盘读取页面。 将页面复制到数据库快照 (写入时复制) 。 关闭数据库文件和文件解压缩。 BACKUPIO当备份任务正在等待数据或正在等待缓冲区存储数据时发生。 此类型并不常见，除非任务正在等待磁带装载。 |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
| CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |