檢測和解決 SQL Server2000 SP4中問題

像 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: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 利用率提高。很多時候，如果新增了索引或者發生了其他調整操作，則可以調整查詢以使其更快地執行並使用更少的資源。這不僅提高了相關查詢的效能，而且還提高了系統的整體效能。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-369031/，如需轉載，請註明出處，否則將追究法律責任。