在 SQL Server 中的診斷能幫助您偵測已停止,並無法脫離的 I/O 作業

摘要

資料庫管理系統 (DBMS),例如 SQL Server 依賴時效性的檔案輸入和輸出 (I/O) 作業。其中一種下列項目可能會造成無法脫離或已停止的 I/O 作業,SQL Server 的回應速度和效能造成不良的影響︰

  • 有瑕疵的硬體

  • 不正確地設定的硬體

  • 韌體設定值

  • 篩選器驅動程式

  • 壓縮

  • 錯誤

  • I/O 路徑中的其他條件

這些 I/O 問題可能會導致發生下列行為︰

  • 封鎖

  • 閂鎖爭用和逾時

  • 回應時間緩慢

  • 自動縮放的資源界限

SQL Server 從 Microsoft SQL Server 2000 Service Pack 4 (SP4) 開始,包括協助偵測停止了,資料庫 I/O 讀取和寫入記錄檔案 I/O 讀取和寫入的條件的邏輯。當 I/O 作業已經被暫止,15 秒或更久時,SQL Server 就會執行下列步驟︰

  1. 偵測到的作業已經暫止

  2. SQL Server 錯誤記錄檔中寫入資訊的訊息

    記錄檔訊息的文字類似下列︰

資訊訊息的說明

訊息文字

描述

<數字 >

「 讀取 」 或 「 寫入作業,在 15 秒內未完成的 I/O 要求數目。

檔案資訊

完整檔案名稱、 資料庫名稱,以及資料庫識別碼 (DBID)。

控制代碼

檔案的作業系統控制代碼。可以使用作業系統控制代碼與偵錯工具或其他公用程式,來幫助追蹤 I/O 要求封包 (IRP) 要求。

位移

最後一個位移卡住 I/O 作業,或最後一個停止了 I/O 作業。可以使用位移與偵錯工具或其他公用程式,來幫助追蹤 IRP 要求。

注意當資訊的訊息寫入 SQL Server 錯誤記錄檔時,I/O 作業可能會不再卡住或是停止了。

此資訊訊息表示,目前的負載可能會遇到下列情況之一︰

  • 超過工作負載的 I/O 路徑的功能。

  • 工作負載超過目前的系統功能。

  • I/O 路徑不明有故障的軟體;也許韌體或驅動程式問題。

  • I/O 路徑不明有故障的硬體元件。

如需有關 SQL Server 2000年的 I/O 模式的詳細資訊,請前往下列 Microsoft 網站︰

http://technet.microsoft.com/en-us/library/cc966500.aspx注意TechNet 文件也會適用於 Microsoft SQL Server 2005年及更新版本。

更多的資訊

無法脫離的 I/O 和已停止的 I/O

無法脫離的 I/O

無法脫離的 I/O 被定義為未完成的 I/O 要求。通常,無法脫離的 I/O 表示無法脫離的 IRP。若要解決無法脫離的 I/O 條件,您通常必須重新啟動電腦或執行類似的動作。無法脫離的 I/O 條件通常會指示下列其中一項︰

  • 有瑕疵的硬體

  • I/O 路徑元件中的錯誤

已停止的 I/O

已停止的 I/O 被定義為 I/O 要求的未完成,或是採用過多的時間才能完成。由於下列原因之一通常會發生停滯的 I/O 行為︰

  • 硬體組態

  • 韌體設定值

  • 需要協助的硬體或軟體廠商,以追蹤並解決一個篩選器驅動程式問題

SQL Server 停止 I/O,I/O 記錄和報告

Microsoft SQL Server 支援處理許多情況下每年涉及阻塞或已停止的 I/O 問題。這些 I/O 問題出現不同的方式。I/O 問題都是一些最困難的問題診斷和偵錯,並進行偵錯與 Microsoft 和客戶需要大量的時間和資源。報告的功能已加入至 SQL Server 2000 SP4 或更新版本可大幅減少所需識別 I/O 問題的時間。

報告和錄製的 I/O 要求的設計是以每個檔案為基礎。偵測和報告已停止,並無法脫離的 I/O 要求的是兩個不同的動作。

錄製

有兩個 SQL Server 中的記錄動作發生時的時間。首先是實際完成 I/O 作業時。如果 I/O 要求會需要超過 15 秒鐘來完成,就會發生記錄作業。延遲寫入器執行時,就會為第二個一點時間。延遲寫入器執行時,延遲寫入器會檢查所有待處理的資料,並擱置的記錄檔檔案 I/O 要求。如果已經超過 15 秒的臨界值,就會發生記錄作業。

報告

報告就會發生在距離是 5 分鐘或更多的時間間隔。在檔案上進行下一個 I/O 要求時,報告就會發生。如果記錄動作發生,而且在 5 分鐘或更有自從上次報告發生,< 結論=""> 一節所述的資訊訊息會寫入到 SQL Server 錯誤記錄檔。

15 秒的臨界值不是可調整的。不過,您可以停用已停止或無法脫離 I/O 偵測使用追蹤旗標 830,雖然我們不建議您採用這種。

若要停用偵測,SQL Server 啟動時,請使用-T830來停用偵測,每次啟動該 SQL Server 時的啟動參數。若要停用目前正在執行的 SQL Server 的執行個體的偵測,請使用下列的陳述式︰

dbcc traceoff (830,-1)此設定才會生效,只針對 SQL Server 處理序的生命週期。

注意變成停止了或停滯的 I/O 要求會報告一次。例如,如果訊息報告 10 個 I/O 要求會延遲幾,這些 10 的報告將不會再發生。如果下一個訊息報告 15 的 I/O 要求會延遲幾,這表示已成為延遲 15 個新的 I/O 要求。

追蹤 I/O 要求封包 (IRP)

SQL Server 會使用標準的 Microsoft Windows API 呼叫來讀取和寫入資料。例如,SQL Server 會使用下列功能︰

  • WriteFile

  • ReadFile

  • WriteFileScatter

  • ReadFileGather

讀取或寫入的要求是由 Windows 處理,以 I/O 要求封包 (IRP)。如果要判斷 [IRP 狀態,請使用下列兩個動作︰

  • Microsoft 平台支援協助

  • 核心偵錯工具

如需有關 IRP 和 IRP 追蹤,請移至下列 Microsoft 網站,並搜尋關鍵字"IRP 」:

http://support.microsoft.com注意核心偵錯可能會破壞現有程式碼的處理程序,因為核心偵錯可能需要先停止系統,才能完成偵錯動作。我們建議您檢查下列項目的任何可用的更新︰

  • BIOS

  • 韌體

  • 其他的 I/O 路徑元件

執行偵錯的其他動作之前,請連絡您的硬體廠商。協力廠商驅動程式、 韌體或篩選器驅動程式元件,可能會牽涉到偵錯工作階段。

系統效能及查詢計劃的動作

整體系統效能也可以扮演關鍵角色,I/O 處理中。當您正在調查報告已停止或無法脫離的 I/O 作業時您應該列入考量的系統的一般健全狀況。過多的負載,可能會造成整體的系統,才會變慢。這包括 I/O 處理。次發生這個問題時系統的行為可以判斷問題的根本原因的關鍵因素。例如,是否 CPU 使用率變高或發生問題時 CPU 使用率依舊很高,這種行為可能表示系統上的處理序正在使用太多的 CPU,有不利影響其他處理程序。

效能計數器

若要監視的 I/O 效能,請檢查下列效能計數器的特定的 I/O 路徑資訊︰

  • 平均磁碟秒/傳輸

  • 平均磁碟佇列長度

  • 目前磁碟佇列長度

例如,在執行 SQL Server 的電腦上的平均磁碟秒/傳輸時間通常是少於 15 毫秒。 如果分枝的平均磁碟秒/傳輸值,這表示,I/O 子系統無法以最佳方式跟的上的 I/O 要求。

當您使用 [效能] 計數器,因為 SQL Server 會以完整利用大量發送磁碟佇列長度的非同步 I/O 功能時,則要小心。因此,較長的時間磁碟佇列長度本身並不表示有問題。

在 [Windows 系統監視器] 中,您可以檢閱計數器 「 實體磁碟︰ 磁碟的位元組/秒 」 受影響的每個磁碟,以及比較對計數器 」 處理程序:: IO 資料位元組數/秒 」 的活動的速率和"程序︰ IO 其他位元組/秒 」 每個處理序來識別是否一組特定的處理程序會產生過多的 I/O 要求。有各種其他 I/O 相關的處理序中的可用計數器物件,會顯示更細微的資訊。如果您判斷 SQL Server 執行個體,負責在伺服器上的過多 IO 載入,請檢閱"索引和平行處理原則 」 的下一個區段。偵測和解決 I/O 瓶頸的詳細討論,檢閱 MSDN (英文) 白皮書疑難排解效能問題,在 SQL Server 2008年中的 I/O 瓶頸 」 一節或SQL Server 2005 中的 [疑難排解效能問題

索引和平行處理原則

通常,因為索引已遺失,就會發生的 I/O 高速量。這種行為可以嚴重推入 I/O 路徑不明。使用索引開啟精靈 (ITW) 是通過可以幫助您解決 I/O 系統上的壓力。如果查詢受益的索引,而不是資料表掃描,或可能是它會使用排序或雜湊,如果系統能夠獲得下列優點︰

  • 減少是由實體 i/o 所需完成的查詢的效能優勢會直接建立的動作。

  • 資料快取中的分頁數目較少,就必須開啟。因此,在資料快取中之網頁保持作用中查詢與相關的。

  • 因為索引可能會遺失,或是已過期的統計資料,會使用排序和雜湊。您可能會新增一或多個索引,來降低tempdb使用和爭用情況。

  • 減少是由資源、 平行作業,或這兩者。由於 SQL Server 並不保證平行查詢執行,而且會被視為系統的負載,最好是最佳化的序列執行的所有查詢。若要最佳化查詢,請開啟 Query Analyzer sp_configure 值的最大平行度] 選項設為1。如果要迅速成為序列的作業執行會微調所有的查詢,平行執行往往是只是較好的效果。 不過,通常可以平行執行已選取的資料量大小剛剛好,因為。 遺漏的索引,大型的排序可能會發生。 執行排序作業的多個工作人員會建立更快速的回應。 不過,此巨集指令可以大幅增加的系統上的壓力。 較大的讀取的要求,從許多工作者從多個工作者造成 CPU 使用率的增加與 I/O 降到尖峰。 可以調整多次的查詢,執行速度,並使用較少的資源,如果索引會被加入,或者發生其他的微調動作。

從 Microsoft SQL Server 支援的實用範例

下列範例有已處理,Microsoft SQL Server 支援的平台擴大規模的支援。 這些範例被為了提供參照框架及您的期望即將停止,I/O 的情況下的說明集合,以及有關系統可能會受到影響或可能的方式回應。 沒有特定的硬體或驅動程式會造成任何特定的風險或更高的風險,透過另一個集合。所有系統都都在這方面相同的。

範例 1︰ 記錄檔寫入停留 45 秒

嘗試定期寫入 SQL Server 記錄檔會變成卡約 45 秒。記錄檔寫入未及時完成。此行為產生導致 30 秒的用戶端逾時的封鎖條件。

應用程式送出至 SQL Server 中,某一交付並認可變得為擱置中的記錄檔寫入卡住。這個行為會造成查詢繼續持有鎖定,並封鎖來自其他用戶端的連入要求。然後,其他用戶端啟動的逾時時間。因為應用程式不會復原開啟的交易查詢逾時發生時,這會難上加難問題。這會建立數百個開啟的交易持有鎖定。因此,就會發生嚴重的封鎖的情況。

如需有關處理與封鎖的交易的詳細資訊,請參閱下列微軟知識庫文件︰

224453 的了解並解決 SQL Server 封鎖問題應用程式服務使用連接共用的網站。更多連線會變成封鎖,網站會建立更多連線。這些連線會變成封鎖,並在循環會繼續。

大約 45 秒之後,記錄檔寫入完成。不過,這段時間,由數百個連線備份起來。封鎖的問題會造成幾分鐘的復原時間的時間,SQL Server 和應用程式。已停止的 I/O 條件加上應用程式的問題,有非常負面的影響,在系統上。

解決方案

這個問題是無法脫離的 I/O 要求主機匯流排介面卡 (HBA) 驅動程式中追蹤。電腦有多個的 HBA 卡與容錯移轉支援。當 1 個 HBA 後面,或不通訊與存放區域網路 (SAN) 中時,"容錯移轉之前重試"逾時值設定為 45 秒。超過逾時,而的 I/O 要求傳送至第二個 HBA。第二個 HBA 處理要求,並快速地完成。為了協助預防這類的停車格條件,硬體製造商所建議"容錯移轉之前的重試"設定值為 5 秒。

範例 2︰ 篩選器驅動程式操作

許多防毒軟體程式及備份產品使用 I/O 篩選器驅動程式。 這些 I/O 篩選器驅動程式,會成為 「 I/O 要求堆疊中的一部份,且有 IRP 要求的存取。 從建立的錯誤的各種問題卡住 I/O 條件,或是停止篩選器驅動程式實作中的 I/O 條件,從未見過 Microsoft 產品支援服務。

一個這類條件是允許的過備份後所開啟的檔案備份的備份處理的篩選器驅動程式。系統管理員必須包含 SQL Server 資料檔目錄中檔案的備份選項。發生備份時,備份會嘗試在備份開始的時間收集正確的映像的檔案。如此一來,延遲 I/O 要求。允許 I/O 要求完成一次只有一個,如同它們由軟體所處理。

備份啟動時,SQL Server 效能大幅減輕因為 SQL Server 的 I/o 被迫完成一次。複合問題,"one 一次"邏輯的是,無法以非同步方式執行 I/O 作業。因此,SQL Server 必須完成張貼 I/O 要求,並繼續執行,背景工作陷入 「 讀取 」 或 「 寫入呼叫直到 I/O 要求完成為止。處理工作,例如 SQL Server 先期讀取已有效地停用篩選器驅動程式的動作。此外,篩選器驅動程式中的另一個錯誤處於"one 一次"動作程序,即使在備份已完成。還原 SQL Server 效能的唯一方法是關閉,然後再重新開啟資料庫,或重新啟動 SQL Server,這樣檔案控制代碼已釋放,和不使用的篩選器驅動程式互動的情況下,為重新取得。

解決方案

若要解決這個問題,SQL Server 資料檔已移除檔案的備份程序。軟體製造商也會修正剩下"one 一次"模式中的檔案的問題。

範例 3︰ 隱藏的錯誤

許多高階系統有多頻道的 I/O 路徑,以處理負載平衡或類似的活動。Microsoft 產品支援發現載入平衡的軟體 I/O 要求失敗,但軟體未正確處理錯誤狀況的問題。軟體可以試著無限的重試。在 I/O 作業就會變成卡住,而 SQL Server 無法完成指定的動作。只有在記錄檔寫入稍早所述的條件,像很多此類狀況 wedges 系統之後,就可能會發生許多不佳的系統行為。

解決方案

若要解決這個問題,請重新啟動 SQL Server 則通常需要。不過,有時候您必須重新啟動系統還原處理。我們也建議您向 I/O 廠商,取得軟體更新。

範例 4︰ 遠端存放、 鏡像及 raid 磁碟機

許多系統使用鏡像,或採取類似的步驟,以避免資料遺失。某些使用鏡像的系統是以軟體為基礎而有些則以硬體為基礎。通常由 Microsoft 支援發現這些系統的情況是增加的延遲時間。

I/O 視為完成之前,將必須完成到鏡像的 I/O 時,就會發生增加的整體的 I/O 時間。對於遠端鏡像的安裝,可以成為涉及網路重試次數。當發生磁碟機失敗並 raid 系統重建時,也可中斷的 I/O 模式。

解決方案

嚴格的組態設定是需要減少至鏡像或 raid 重建作業的延遲。


如需詳細資訊,檢閱SQL Server,以支援遠端鏡像的資料庫使用者的需求

範例 5︰ 壓縮

Microsoft 不支援,Microsoft SQL Server 7.0 或 Microsoft SQL Server 2000年資料檔和記錄檔壓縮磁碟機上。 NTFS 壓縮不是安全的 SQL Server 的因為 NTFS 壓縮中斷寫入記錄 (WAL) 通訊協定。 NTFS 壓縮也需要日益繁重的處理,每個 I/O 作業。 壓縮會建立"一次 one"會導致嚴重的效能問題發生的行為類似。

解決方案

若要解決這個問題,請解壓縮資料和記錄檔。

如需詳細資訊,請檢閱支援 SQL Server 資料庫置於壓縮磁碟機的描述

其他資料點

Sys.dm_os_wait_stats 動態管理檢視 (DMV) 中的 PAGEIOLATCH_ * 和 writelog 等待是調查路徑的 I/O 效能的主要指標。如果您看到顯著的 PAGEIOLATCH 等待,這表示 SQL Server 正在等候 I/O 子系統上。PAGEIOLATCH 等待一段是一般和預期行為。不過,如果 PAGEIOLATCH 等待時間的平均值一直大於 10 毫秒 (ms),您應該調查 I/O 子系統受到壓力。如需詳細資訊,請參閱下列文件︰



參考

SQL Server 需要系統支援 < 保證的傳遞到穩定的媒體="" 」,在="" [="">

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×