檢測和解決 SQL Server 2000 SP 4 中的延遲和阻塞 I/O 問題

檢測和解決 SQL Server 2000 SP 4 中的延遲和阻塞 I/O 問題發布日期： 7/13/2005 | 更新日期： 7/13/2005

Robert Dorr
Microsoft Corporation

摘要：特約專欄作家 Robert Dorr 探討了 SQL Server 2000 Service Pack 4 中的報告工具如何顯著減少為識別和確定延遲和阻塞 I/O 操作的根源所花費的時間。

本頁內容

	簡介
	記錄與報告
	效能和計劃操作
	索引和並行性
	來自 Microsoft SQL Server Support 的實際樣本
	小結
	參考部分

簡介

像 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 錯誤記錄檔中：

2004-11-11 00:21:25.26 spid1 SQL Serverhas encountered 192 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [E:/SEDATA/stressdb5.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 可能不再阻塞或延遲。）

有關 SQL Server 2000 I/O 模式的更完整資訊，請參閱 SQL Server 20I/O Basics。

返回頁首

記錄與報告

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 模式的程式錯誤。

這時，如果我們已經具有了 SQL Server 2000 SP4 對延遲 I/O 操作進行報告的功能，那麼我們在疑難解答過程中就可以很快知道基本問題是什麼。

樣本 3 — 隱藏的錯誤

很多高端系統具有用於處理Server Load Balancer的多通道 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 等待類型。

返回頁首

小結

儘管阻塞和延遲 I/O 問題在 SQL Server 部署中很罕見，但從曆史上來看，這些問題一旦發生，就非常難以解決。因為此類問題的根源通常存在於驅動程式或硬體裝置中，所以調查和解決這類問題可能花費大量的時間，並且需要具有超出典型資料庫管理員能力範圍的專業技能。使用 SQL Server 2000 SP4 中的新工具可以顯著減少解決此類問題所需的時間，並且最起碼可以為 DBA 指明正確的方向。

返回頁首

參考部分

231619: How to use the SQLIOStress utility to stress a disk subsystem such as SQL Server

826433: PRB: Additional SQL Server Diagnostics Added to Detect Unreported I/O Problems

230785: INF: SQL Server 7.0 and SQL Server 2000 Logging and Data Storage Algorithms Extend Data Reliability

適合於開發人員的 SQL Server

Robert Dorr 自 1994 年以來一直擔任 Microsoft SQL Server 支援人員部門的進階 SQL Server 升級工程師。Robert 已經在 MSDN 和 Microsoft Technet 上發布了各種有關 SQL Server 引擎的文章，他還是 SQL Pass 的示範者以及關鍵的 SQL Server 公用程式（Read80Trace 和 SQLIOStress）的作者。Robert 與他的妻子和孩子一起居住在美國德克薩斯州達拉斯的郊區。

轉到原英文頁面