像 SQL Server 這樣的數據庫管理系統依賴于文件輸入/輸出操作的及時進行。有故障或配置不當的硬件、固件設置、篩選器驅動程序、壓縮、程序錯誤以及 I/O 路徑內的其他情況都可能導致阻塞或延遲 I/O 問題，并且很快對 SQL Server 性能產生消極影響。

上述問題對 SQL Server 的影響因問題細節的不同而差異很大，但它們通常導致阻塞、鎖存器爭用和超時、過長的響應時間以及資源的過度利用。

阻塞 I/O 是指必須進行外部干預才能完成的 I/O 請求（通常是 I/O 請求包 (IRP)）。這種狀況通常需要執行完整的系統重新啟動或類似操作才能解決，并且強烈表明硬件有故障或者在 I/O 路徑組件中存在程序錯誤。

延遲 I/O 是指無需干預即可完成但所花時間超過預期時間的 I/O 請求（同樣，這通常是 IRP）。這種狀況的原因通常是硬件配置、固件設置或篩選器驅動程序干預，需要硬件或軟件供應商提供幫助以便跟蹤和解決。

SQL Server 2000 SP4 包含數據庫和日志文件 I/O（讀和寫）邏輯以便檢測延遲和阻塞狀況。當 I/O 操作經過 15 秒鐘或更長時間仍未完成時，SQL Server 會檢測到并報告這一狀況。以下消息將被記錄到 SQL Server 錯誤日志中：

2007-11-1 00:21:25.26 spid1 SQL Serverhas encountered 192 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [E:SEDATAstressdb5.ndf] in database [stressdb] (7). The OS file handle is 0x00000000000074D4. The offset of the latest long IO is: x00000000022000'.

該消息表明，當前工作負載需求超出了 I/O 路徑或當前系統配置和功能，或者 I/O 路徑含有不能正常工作的軟件（固件、驅動程序）或硬件組件。

所記錄的錯誤信息提供了以下信息：

• ### occurrences — 未能在 15 秒鐘以內完成讀或寫操作的 I/O 請求的數量。

• File information — 完整的文件名、數據庫名和受影響文件的 DBID。

• File handle — 該文件的操作系統句柄。可以通過調試器和其他實用工具來使用這一信息跟蹤 IRP 請求。

• Offset — 上一個阻塞或延遲 I/O 的偏移量。可以通過調試器和其他實用工具來使用這一信息跟蹤 IRP 請求。（注：在記錄該消息的時候，該 I/O 可能不再阻塞或延遲。）

記錄與報告 I/O 的報告和記錄是按照文件執行的。延遲和阻塞 I/O 請求的檢測和報告是兩個不同的操作。

檢測（記錄）是在 SQL Server 內部的兩個位置處理的。第一個位置是在 I/O 實際完成的時候。如果請求花費了 15 秒鐘以上，則發生記錄操作。第二個位置是在延遲寫入器進程執行的時候。當延遲寫入器執行時，它包含新的對所有掛起的數據和日志文件 I/O 請求進行檢查的操作，并且，如果已經超過了 15 秒鐘的閾值，則會發生記錄操作。

報告是按照不低于 5 分鐘的時間間隔執行的。當對文件進行下一次 I/O 請求時，發生報告操作。如果記錄操作已經發生，并且自上一次報告發生以來已經過去了 5 分鐘或更長時間，則向錯誤日志中寫入新的報告（上面顯示的錯誤消息）。

15 秒鐘的閾值當前是不可調整的。盡管不推薦這樣做，但您可以用跟蹤標志 830 完全禁用延遲和阻塞 I/O 檢測。在 SQL Server 啟動期間設置啟動參數 –T830 可以禁用延遲/阻塞 I/O 檢測。使用 dbcc traceon(830, -1) 可以禁用對當前正在運行的 SQL Server 實例的檢測。只有重新啟動 SQL Server，Dbcc traceon 才會生效。

注：延遲或阻塞的給定 I/O 請求只會報告一次。如果消息報告 10 個 I/O 被延遲，則這 10 個報告不會再次發生。如果下一個消息報告 15 個 I/O 被阻塞，則表明 15 個新的 I/O 請求已經被延遲。

性能和計劃操作

總體系統性能可能在 I/O 處理中扮演關鍵的角色。在研究延遲或阻塞 I/O 的報告時，應該考慮系統的綜合運行狀況。過多的負載可能導致整個系統（包括 I/O 處理）變慢。系統在發生問題時的行為可能是確定問題根源的關鍵所在。例如，如果 CPU 利用率在發生問題時變高或者保持較高水平，則可能表明系統中的某個進程正在消耗如此之多的 CPU 時間，以至于它以各種方式對其他進程產生了消極影響。

請查看性能計數器 Average Disk Sec/Transfer 以及 Average Disk Queue Length 或 Current Disk Queue Length，以獲得特定的 I/O 路徑信息。例如，SQL Server 計算機上的 Average Disk Sec/Transfer 通常低于 15ms。如果該值上升，則可能表明 I/O 子系統無法滿足 I/O 要求。

請記住，SQL Server 充分利用了 Windows 的異步 I/O 功能，并且猛烈地擴展磁盤隊列長度，因此上述性能計數器具有較高的值本身并不表明存在問題。

索引和并行性

特別常見的一種情況是，因為索引丟失以及由此導致的掃描、哈希和排序對 I/O 系統造成的壓力，所以突發大量的 I/O。運行一遍“Index Turning Wizard”通常會有助于解決系統的 I/O 壓力。如果添加索引可以幫助查詢避免表掃描甚至排序或哈希，則系統可以獲得多個優點：

• 減少完成操作所需的物理 I/O，這直接等效于提高查詢的性能。

• 數據緩存中只有較少的頁面必須周轉，因此緩存中的那些頁面可以一直與活動查詢相關。

• 避免不必要的排序和哈希。

• 可以降低 tempdb 利用率和減少爭用情況。

• 減少資源利用率和/或并行操作。因為 SQL Server 不能保證服務器在確定是否將查詢并行化時考慮并行查詢執行和系統中的負載，所以您最好針對串行執行優化所有查詢。在 Q/A 環境中，應該將 max degree of parallelism 設置為 1，以便對根本沒有從服務器收到任何并行計劃的最糟糕情況強行進行調整。如果在測試環境中證實查詢可以按串行方式高效執行，則生產環境中的并行計劃可以提供出乎意料的性能改進。但是，很多情況下，SQL Server 選擇并行執行，這是因為要遍歷數據的絕對數量過于龐大。該數據量通常直接受到索引的影響。例如，如果丟失索引，則可能產生大量排序操作。我們很容易就可以看出，執行排序操作的多個輔助進程如何使響應速度比以串行方式處理排序更快速，不過我們需要了解，該操作可能大幅增加 I/O 系統的壓力。當多個輔助進程并發運行時，來自多個輔助進程的大型讀請求可能導致 I/O 突發以及 CPU 利用率提高。很多時候，如果添加了索引或者發生了其他調整操作，則可以調整查詢以使其更快地運行并使用更少的資源。這不僅提高了相關查詢的性能，而且還提高了系統的整體性能。來自 Microsoft SQL Server Support 的實際示例 Microsoft SQL Server 和 Platforms Escalation Support 已經處理了下列方案，這些方案旨在提供一個參考框架，并且幫助樹立有關延遲和阻塞 I/O 情況以及系統可能如何受到影響的預期。不存在給其他軟硬件帶來任何特殊或更高風險的特殊硬件或驅動程序集；在這個方面，所有系統都是相同的。

示例 1 — 阻塞 45 秒鐘的日志寫操作：

一個嘗試性的 SQL Server 日志文件寫操作周期性地阻塞 45 秒鐘。該日志寫操作無法及時完成，從而產生阻塞情況，導致 30 秒鐘的客戶端查詢超時。

請求被提交并阻塞（日志寫掛起），導致查詢繼續占用鎖并且阻塞來自其他客戶端的傳入請求。其他客戶端開始超時并且使問題變得復雜，這是因為應用程序沒有被設計為在發生超時的時候回滾尚未解決的事務。這會導致數以百計尚未解決的事務占用鎖以及嚴重的阻塞。（有關事務處理和阻塞的詳細信息，請參閱 INF: Understanding and Resolving SQLServer 7.0 or 2000 Blocking Problems）。應用程序使用連接池來維護 Web 站點，因此，隨著更多的連接被阻塞，Web 站點創建了更多的連接，而這些連接又會被阻塞，該循環會一直持續下去。

在大約 45 秒鐘之后，該日志寫操作將完成，但到此時為止，數以百計的連接已經積累起來，從而導致阻塞問題，并使得 SQL Server 和應用程序需要花費幾分鐘的時間進行恢復。當與應用程序問題結合起來的時候，延遲 I/O 狀況會對系統產生非常消極的影響。

解決辦法：這歸因于 HBA 驅動程序中的延遲 I/O 請求。計算機具有多個帶有故障轉移支持的 HBA 卡。故障轉移超時值被配置為 45 秒。當一個 HBA 落后或者在 45 秒鐘或更長時間內未與 SAN 通信時，該 I/O 請求被路由到第二個 HBA 進行處理，并且會很快完成。硬件產品的推薦故障轉移設置為 5 秒鐘，以便避免出現這樣的延遲狀況。 如果在 SQL Server 2000 SP4 中已經有了新的自動報告該狀況的功能，那么我們在疑難解答過程中就可以很快知道，基本問題是由于 SQL Server 外部的問題而發生的阻塞或延遲 I/O 操作。事實上，我們花費了大量時間來解決一個在最初呈現為普通性能問題的問題。

示例 2 — 篩選器驅動程序干預：

許多防病毒軟件和備份產品使用 I/O 篩選器驅動程序。這些篩選器驅動程序成為 I/O 請求棧的一部分，并且可以訪問 IRP 請求。Microsoft 技術支持部門已經遇見過各種問題 — 從導致阻塞 I/O 的錯誤到篩選器驅動程序實現中的延遲狀況。

其中，Microsoft SQL Server 技術支持部門遇到的一種情況是，涉及到用于備份處理（該過程能夠備份在備份時處于打開狀態的文件）的篩選器驅動程序。系統管理員錯誤地在文件備份選擇中包括了 SQL Server 數據文件目錄。當備份發生時，它試圖收集備份開始時文件的一致鏡像。在完成該操作時，它將延遲后續的 I/O 請求，使它們能夠在軟件處理它們時逐個完成。

當備份開始時，SQL Server 的性能會急劇下降，因為針對 SQL Server 的 I/O 被強迫逐個完成。使該問題變得更為復雜的是，單 I/O 邏輯的特點使得 I/O 通常無法異步執行，因此當 SQL Server 期望發送 I/O 請求并繼續工作時，UMS 輔助進程卻在 I/O 完成之前一直阻塞在讀或寫調用中。SQL Server 預讀功能實際上被篩選器驅動程序的操作禁用了。而且，即使當備份完成時，篩選器驅動程序中的另一個程序錯誤仍然使單 I/O 行為保持不變。恢復 SQL Server 性能的唯一方法是關閉數據庫并重新打開它或者重新啟動 SQL Server，以便在當前篩選器驅動程序交互未就緒的情況下釋放并重新獲取文件句柄。

解決辦法：將 SQL Server 的數據文件從文件備份過程中排除，并且解決篩選器驅動程序中的導致文件被置于單 I/O 模式的程序錯誤。

示例 3 — 隱藏的錯誤：

很多高端系統具有用于處理負載平衡的多通道 I/O 路徑以及類似的工具。Microsoft SQL Server 技術支持部門已經見過使用此類軟件的情況，其中，盡管 I/O 請求失敗，但軟件確實正確地處理了錯誤狀況，并且執行了無數次重試。I/O 被阻塞，并且 SQL Server 無法完成指定的操作。與上面描述的日志寫狀況非常類似，在這樣的狀況對系統產生了消極影響之后，發生了很多糟糕的系統行為。

解決辦法：在類似情況下，重新啟動 SQL Server 可以在一定程度上緩解問題，但是，有時需要重新啟動 Windows 來使處理恢復到正常狀態。當然，I/O 子系統中的程序錯誤最終需要由 I/O 供應商解決。

SQL Server 2000 SP4 的新的對此類狀況進行自動報告的功能使得類似問題的檢測變得更加容易。我們不僅可以看到整個服務器的總體性能下降，而且還可以通過 SP4 所記錄的新消息洞察問題的本質，并且知道該問題很可能出在 SQL Server 外部。

示例 4 — 遠程存儲/鏡像/RAID 驅動器：

很多系統使用鏡像或類似的技術來幫助防止丟失數據。其中一些系統是基于軟件的，而其他系統是基于硬件的。Microsoft SQL Server 技術支持部門經常遇到的與這些系統有關的情況是延遲增加。

當針對鏡像的 I/O 必須在 I/O 操作被視為完成之前成功完成時，這顯然會增加總體 I/O 時間。對于遠程鏡像安裝，網絡延遲和重試可能成為一個不利因素。當發生驅動器故障并且 RAID 子系統重新生成時，I/O 吞吐量可能會受到影響。

解決辦法：在類似情況下，我們通常建議使用嚴格的配置設置（這隨供應商和設備而異），以減少鏡像延遲和 RAID 重新生成操作。

RAID 系統開銷和延遲可能導致 I/O 變慢，而 SQL Server 對此無能為力。就像任何其他應用程序一樣，它是 RAID 硬件和驅動程序的客戶端。當該類型的問題使服務器的速度過度降低時，SP4 中新的延遲和阻塞 I/O 報告功能有助于查明問題所在。

示例 5 — 壓縮：

Microsoft 不在壓縮驅動器上支持 SQL Server 7.0 或 2000 數據和日志文件。NTFS 壓縮是不安全的，這不僅是因為它破壞了預寫日志 (WAL) 協議，而且還因為它要求對每個 I/O 請求執行更多的處理。壓縮禁止了異步 I/O，從而導致所有帶有受影響數據或日志文件的 SQL Server I/O 都被同步執行。

解決辦法：在這種情況下，我們總是建議客戶解壓縮他們的數據和日志文件。

NTFS 壓縮可能導致 I/O 變慢，而 SQL Server 對此無能為力。就像任何其他用戶模式應用程序一樣，它是文件系統的客戶端。當壓縮對 SQL Server I/O 操作產生不利影響時，SP4 中新的延遲和阻塞 I/O 報告功能有助于查明問題所在。

附加數據點

系統進程中提供的等待類型信息可能有助于診斷 I/O 瓶頸。緩沖區 I/O 鎖存器等待類型和寫日志等待是調查 I/O 路徑性能的關鍵指標。Microsoft 知識庫文章 822101: The waittype and lastwaittype fields in the sysprocesses table 概述了等待類型，并且詳細介紹了與診斷延遲或阻塞 I/O 狀況有關的 I/O 等待類型。