針對 I/O 所造成 SQL Server 效能緩慢的問題進行疑難排解

發行項
11/20/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 要求。當它張貼 I/O 要求時，SQL Server 會逾時要求，並使用等候類型報告要求的持續時間。 SQL Server 會使用等候類型來指出產品中不同位置的 I/O 等候。 I/O 相關等候如下：

PAGEIOLATCH_SH / PAGEIOLATCH_EX
WRITELOG
IO_COMPLETION
ASYNC_IO_COMPLETION
BACKUPIO

如果這些等候持續超過 10-15 毫秒，I/O 就會被視為瓶頸。

注意

為了提供內容和觀點，在針對 SQL Server 進行疑難解答的世界中，Microsoft CSS 觀察到 I/O 要求超過一秒且每個傳輸這類 I/O 系統需要優化 15 秒的情況。相反地，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 中開啟筆記本

選項 2：手動遵循步驟

這些步驟概述方法：

步驟 1：SQL Server 報告 I/O 速度緩慢嗎？

SQL Server 可能會以數種方式回報 I/O 延遲：

I/O 等候類型
DMV 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 數據行以瞭解延遲詳細數據。

Errorlog 或 Application 事件記錄檔中回報的錯誤 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 特定實例的（最常見的罪魁禍首）和 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、記憶體）
- 超過 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 。一般而言，建議將數據和記錄檔保留在個別的磁碟區上。事務歷史記錄寫入是循序寫入，而從數據檔讀取或寫入數據是隨機的。將一個磁碟區上的數據和記錄檔混合在一個磁碟區（尤其是傳統的旋轉磁碟驅動器）會導致磁碟前端移動過多。
太多 VDF：太多虛擬記錄檔（VFS）可能會導致 WRITELOG 等候。太多 VDF 可能會導致其他類型的問題，例如長時間復原。
太多小型交易：雖然大型交易可能會導致封鎖，但太多小型交易可能會導致另一組問題。如果您未明確開始交易，則任何插入、刪除或更新都會導致交易（我們稱之為自動交易）。如果您在迴圈中執行 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 時，大量插入提供者（“Insert Bulk”）會使用此等候類型。
讀取 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中的檔案統計數據

Errorlog 或 Application 事件記錄檔中回報的錯誤 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中的檔案統計數據

Errorlog 或 Application 事件記錄檔中回報的錯誤 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

意見反應

其他資源