針對 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 藉由呼叫、ReadFile()、 WriteFileGather()和 ReadFileScatter()等 WriteFile()Win32 函式，對 OS 提出 I/O 要求。當 SQL Server 張貼 I/O 要求時，會使用等候類型來處理要求並報告要求的持續時間。 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 要求花費超過一秒且每個傳輸最高 15 秒的情況，這類 I/O 系統需要優化。相反地，Microsoft CSS 已看到輸送量低於一毫秒/傳輸的系統。使用現今的 SSD/NVMe 技術，每個傳輸的通告輸送量速率範圍以數十毫秒為單位。因此，10-15 毫秒/傳輸圖是我們根據 Windows 與 SQL Server 工程師多年來的集體體驗所選取的非常近似臨界值。通常，當數字超過這個近似閾值時，SQL Server 用戶會開始看到工作負載的延遲並回報。最後，I/O 子系統的預期輸送量是由製造商、型號、組態、工作負載，以及可能的多個其他因素所定義。

方法論

本文結尾的流程圖說明 CSS 用來處理 SQL Server 緩慢 I/O 問題的方法Microsoft。這不是詳盡或獨佔的方法，但已證明有助於隔離問題並加以解決。

您可以選擇下列兩個選項之一來解決問題：

選項 1：透過 Azure Data Studio 直接在筆記本中執行步驟

注意事項

嘗試開啟此筆記本之前，請確定已在本機計算機上安裝 Azure Data Studio。若要安裝它，請移至瞭解如何安裝 Azure Data Studio。

在 Azure Data Studio 中開啟 Notebook

選項 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_*每個 I/O 要求的值、 WRITELOG和 ASYNC_IO_COMPLETION 和數個其他較不常見等候類型的值通常應保持在 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"

Also, for more information on this error, see the MSSQLSERVER_833 section.

步驟 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 子系統) 的預期輸送量規格。例如，您可以透過 2 GB/秒 HBA 卡或 SAN 交換器上的 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 (最常見的) 問題，並 Page Writes/Sec 針對特定實例 (較不常見的) ，以找出 SQL Server 是否為攻擊者。如果 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、記憶體)
- 超過整個SAN網路的I/O容量 (不平衡，而不只是後端記憶體)
- 驅動程式或韌體問題
硬體廠商和/或系統管理員必須在這個階段參與。
查詢問題： 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 中，已新增最多八個記錄寫入器線程，進而改善輸送量。此外，在 SQL Server 2019 中，每個一般背景工作線程都可以直接進行記錄寫入，而不是張貼到記錄寫入器線程。透過這些改善， WRITELOG 排程問題很少會觸發等候。

ASYNC_IO_COMPLETION

發生於下列一些 I/O 活動發生時：

大量插入提供者 (「大量插入」) 在執行 I/O 時使用此等候類型。
讀取 LogShipping 中的復原檔案，並導向異步 I/O 以進行記錄傳送。
在數據備份期間從數據檔讀取實際數據。

IO_COMPLETION

在等候 I/O 作業完成時發生。這種等候類型通常牽涉到與數據頁無關的 I/O， (緩衝區) 。範例包含：

在溢出期間讀取和寫入磁碟的排序/哈希結果 (檢查 tempdb 記憶體) 的效能。
讀取和寫入急切多任務緩衝處理至磁碟 (檢查 tempdb 記憶體) 。
在任何導致從磁碟讀取記錄檔的作業期間，從事務歷史記錄 (讀取記錄區塊，例如復原) 。
尚未設定資料庫時，從磁碟讀取頁面。
將頁面複製到資料庫快照集 (寫入時複製) 。
關閉資料庫檔案和檔案解壓縮。

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 活動？

原因

方法的圖形表示法

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

意見反應

意見反應

其他資源