適用於：SQL Server
總結
本文提供一步步的方法論，以診斷並解決因磁碟 I/O 瓶頸導致的 SQL Server 效能緩慢。 它說明如何利用SQL Server等待類型、sys.dm_io_virtual_file_stats動態管理檢視和Windows 效能監視器計數器來識別 I/O 延遲。 你將學會如何判斷 I/O 子系統是否過載，SQL Server 是否是 I/O 的主要驅動程式，以及需要調查哪些硬體、查詢、過濾驅動或應用層級的原因。 利用本指南找出 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 要求。 當 SQL Server 發出 I/O 請求時，它會計時該請求，並使用 等候類型報告請求的持續時間。 SQL Server 會使用等候類型來指出產品中不同位置的 I/O 等候。 I/O 相關等候如下：
如果這些等候持續超過 10-15 毫秒，I/O 就會被視為瓶頸。
注意
為了提供背景，Microsoft 曾觀察到 SQL Server 系統中，I/O 請求會超過一秒鐘，甚至每次傳輸最高可達 15 秒。 這類輸入輸出系統需要優化。 相反地，Microsoft 也曾遇過每次傳輸吞吐量低於一毫秒的系統。 以現今的 SSD 和 NVMe 技術，宣稱的傳輸速率可達數十微秒級。
每次傳輸 10–15 毫秒的數字是根據 Windows 與 SQL Server 工程師的集體經驗所選定的近似門檻。 通常，當數量超過這個門檻時，SQL Server 使用者會開始看到工作負載的延遲。 最終，I/O 子系統的預期吞吐量由製造商、型號、組態、工作負載及其他因素所定義。
隔離 I/O 瓶頸的方法論
本文結尾的流程圖說明了解決 SQL Server 緩慢 I/O 問題常用的方法論。 此方法論並非完整或排他性，但有助於隔離並解決問題。
方法在以下步驟中被概述：
步驟 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_*WRITELOGASYNC_IO_COMPLETION、以及其他幾種較少見的等待類型，通常每次I/O請求都應維持在10-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 中報告的資料庫檔案層級延遲，請執行下列查詢：
#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 和分割區管理器（也就是過濾驅動程式）之間。 分割區管理是作業系統收集 Perfmon 計數器的 I/O 層。 若要解決延遲問題，請確定篩選驅動程式的適當排除，並解決篩選驅動程序問題。 像是 防毒軟體、 備份解決方案、 加密、 壓縮等軟體，都會使用過濾驅動程式。 使用此指令列出系統及其所連接磁碟區的濾波器驅動程式。 接著，查閱「 配置的過濾高度 」文章中的驅動程式名稱和軟體廠商。
fltmc instances
如需詳細資訊，請參閱 如何選擇要在執行 SQL Server 的電腦上執行的防病毒軟體。
避免使用加密文件系統 （EFS） 和文件系統壓縮，因為它們會導致異步 I/O 變得同步，因而變慢。 如需詳細資訊，請參閱 異步磁碟 I/O 在 Windows 上顯示為同步一文。
步驟 3：I/O 子系統是否超過容量？
如果 SQL Server 與作業系統指出 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，原因在於經常需要將更多資料庫頁面儲存在有限的可用記憶體中時，所引發的Lazy Writer排清。 - 如果您發現頁面寫入是繁重 I/O 活動的來源，請檢查
Buffer Manager: Checkpoint pages/sec它是否是因為需要大量頁面排清，才能符合復原間隔設定需求。 您可以使用 間接檢查點 來平均分配 I/O 負載，或增加硬體 I/O 輸送量。
SQL Server 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 相關等候類型的相關信息
以下說明涵蓋了您在 SQL Server 中看到的常見等待類型，當磁碟 I/O 問題被回報時。
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等候時間。排程問題導致 Log Writer 執行緒未能及時排程：在 SQL Server 2016 之前，單一 Log Writer 執行緒會執行所有記錄寫入。 如果線程排程發生問題（例如 CPU 使用率過高），記錄寫入線程和記錄刷新可能會延遲。 在 SQL Server 2016 中，已新增最多四個記錄寫入器線程，以增加記錄寫入輸送量。 請參閱 SQL 2016 - 它運行速度更快：多個記錄寫入工作者。 在 SQL Server 2019 中，已新增最多 8 個記錄寫入器線程，進而改善輸送量。 此外，在 SQL Server 2019 中，每個一般的工作執行緒都可以直接執行日誌寫入，而不是傳送到日誌寫入執行緒。 透過這些改善，
WRITELOG由排程問題引發的等候現象將會很少發生。
ASYNC_IO_COMPLETION
發生於下列某些 I/O 活動發生時：
- 執行 I/O 時，大量插入提供者 （“Insert Bulk”） 會使用此等候類型。
- 讀取日誌傳送中的還原檔案，並管理日誌傳送的異步 I/O。
- 在數據備份期間從數據檔讀取實際數據。
IO_COMPLETION（輸入/輸出完成）
在等候 I/O 作業完成時發生。 此等候類型通常涉及與數據頁無關的 I/O（緩衝區）。 範例包含：
- 在溢出期間從磁碟讀取和寫入排序/哈希結果（檢查 tempdb 儲存效能）。
- 讀取和寫入排隊快取到磁碟（檢查 tempdb 儲存空間）。
- 從交易日誌讀取日誌區塊（在任何需要從磁碟讀取日誌的作業期間，例如在復原時）。
- 尚未設定資料庫時，從磁碟讀取頁面。
- 將頁面複製到資料庫快照（寫時複製）。
- 關閉資料庫檔案和檔案解壓縮。
BACKUPIO
當備份工作正在等候數據，或正在等候緩衝區儲存數據時發生。 此類型不常見，除非任務正在等候磁帶掛接。