none
Sql Server Log 中,不斷出現"occurrence(s) of I/O requests taking longer than 15 seconds"訊息,不知該如何解決?! RRS feed

  • 問題

  • 發生問題描述如下:

    1. 於某時間點開始,系統Console轉檔匯入資料的程式,會一直在執行到同一個Sql Query時,發生資料庫TimeOut的錯誤。
    2. 但當我將此SQL Query拿至SSMS執行,卻又相當正常(已執行ClearBuffer),此Sql Query用途為Select資料。
    3. 至Sql Server Log(記錄檔)中查看紀錄,便發現於開始Timeout的時間點,同時開始發生如下資料庫訊息:
      SQL Server has encountered 86 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\XXX\XXX.MDF] in database [XXX] (5).

    此問題,困擾許久,一直找不到正確發生原因,也不知該如何解決。
    目前暫時以重啟資料庫來解決此問題,重啟後,即可正確運作約3周時間,
    但不久後,問題依舊。

    問題資訊大致如上,不知是否有人有相關經驗,可提供方向或方法,
    先感謝各位幫忙~

    Thanks~

    2010年7月9日 上午 05:19

解答

  • 看來這位大大似乎是第一次在此提問,煩請看最上頭公告區的「SQL Server Management 討論區發言規範」,謝謝!因為唯有充分的資訊,大家才能依據自身的經驗、相關資訊 … 等,來協助您!

    建議您先透過 SQL 2005 之後開始提供的動態管理檢視表 sys.dm_io_pending_io_requests 以及 Windows 效能計數器(例如:PhysicalDisk\Average Disk sec/Read、PhysicalDisk\Average Disk sec/Write、PhysicalDisk\Average Disk Queue Length、Paging File\% Usage、Database\Transactions/sec、Database\Write Transactions/sec …等)來檢查是否有任何 I/O 延遲的現象。

    另外,於多 CPU 環境中,當工作負載不高時,CPU 有可能自己跑去睡覺(進入低耗電狀態),此時,SQL 就有可能因為無法正確地判斷出 CPU 整體的工作負載,於是就出現您所提到的那個 I/O 警告訊息。參考資料:當您使用公用程式或變更 CPU 頻率的技術時,SQL Server 的執行時間值可能會不正確


    ☞ 這裡是「免費的討論區」付費支援服務請造訪 此處享受尊榮服務
    如果回覆對您有幫助,請記得按下標示為解答」
    在本討論區使用正體中文(即繁體中文),是對參與的朋友的一種尊重,因此請用本討論區的語言:正體中文。
    • 已標示為解答 Samwang 2010年7月12日 上午 05:34
    2010年7月9日 上午 08:51
    版主
  • 根據 wait type 是 CXPacket 及其他的硬體描述,看來可能是平行處理的問題

    請試著下如下的指令處理看看

    sp_configure 'show advanced options', 1;
    GO
    reconfigure;
    GO


    sp_configure 'max degree of parallelism', 1;
    GO
    reconfigure;
    GO 


    請參考 http://soft.zdnet.com.cn/software_zone/2009/1218/1564615.shtml 及  http://www.cnblogs.com/rickie/archive/2008/03/18/1111965.html

     

    • 已標示為解答 Samwang 2010年7月12日 上午 05:33
    2010年7月9日 下午 10:53
  • Change server option means you disable parallelizm on the server, I prefer to find offending query and put maxdop = 1 in it.
    • 已標示為解答 Samwang 2010年7月13日 上午 01:35
    2010年7月12日 下午 02:15

所有回覆

    1. 問一下,多久之後發生一次呢?
    2. 由描述3來看,似乎是等待硬體 IO 太久了。問一下硬體的等級,如  cpu,RAM
    2010年7月9日 上午 05:25
  • Dear Charles,

    十分感謝您的回覆。

    硬體資訊如下:

    CPU Intel Xeon 7330 MP @2.4G(4核心) x 4
    Memery 8192MB (2G * 4)
    Physical Disks 72 G x(2.5” 15K) x 8
    Logical Disk 72G x 4 (RAID1)

    而發生頻率是,一旦發生,該批次匯入程式即會一直不斷發生相同Timeout問題,
    而同時,Sql Server的Log中,也一段時間就會寫出 occurrence(s) of I/O requests taking longer than 15 seconds...的訊息
    當將此Sql Server重啟後,這次的經驗是,維持了約3周正常運作,
    就在昨天又再度發生相同的錯誤了。

    Thanks a lot ~

    2010年7月9日 上午 05:40
  • 之前若伺服器或 SQL server database engine 重起後,您所說的 3周內是正常的,那可能是記憶體不足所引發的 memory swap to page file 的問題。

    在 Activity Monitor (活動監視器) 上有沒有發現什麼異常呢?如已停止、已暫停、或被 lock 等訊息?

    2010年7月9日 上午 05:49
  • 看來這位大大似乎是第一次在此提問,煩請看最上頭公告區的「SQL Server Management 討論區發言規範」,謝謝!因為唯有充分的資訊,大家才能依據自身的經驗、相關資訊 … 等,來協助您!

    建議您先透過 SQL 2005 之後開始提供的動態管理檢視表 sys.dm_io_pending_io_requests 以及 Windows 效能計數器(例如:PhysicalDisk\Average Disk sec/Read、PhysicalDisk\Average Disk sec/Write、PhysicalDisk\Average Disk Queue Length、Paging File\% Usage、Database\Transactions/sec、Database\Write Transactions/sec …等)來檢查是否有任何 I/O 延遲的現象。

    另外,於多 CPU 環境中,當工作負載不高時,CPU 有可能自己跑去睡覺(進入低耗電狀態),此時,SQL 就有可能因為無法正確地判斷出 CPU 整體的工作負載,於是就出現您所提到的那個 I/O 警告訊息。參考資料:當您使用公用程式或變更 CPU 頻率的技術時,SQL Server 的執行時間值可能會不正確


    ☞ 這裡是「免費的討論區」付費支援服務請造訪 此處享受尊榮服務
    如果回覆對您有幫助,請記得按下標示為解答」
    在本討論區使用正體中文(即繁體中文),是對參與的朋友的一種尊重,因此請用本討論區的語言:正體中文。
    • 已標示為解答 Samwang 2010年7月12日 上午 05:34
    2010年7月9日 上午 08:51
    版主
  • Dear Charles,

    您好,在此感謝您的回覆。
    經您提醒,確實在這問題發生時,有發現該批次匯入執行序,於活動監視器中的的狀態均為已暫停,
    命令為SELECT、等待類型為CXPACKET。

    以上資訊提供給您參考~~
    在此感謝您的回覆~~
    Thanks ~~

    2010年7月9日 下午 12:20
  • Dear Alex版主大,

    您好,先感謝您的回覆。
    很不好意思,沒注意到發言規範,下次發問我會多注意資訊提供部分,盡可能提供完整資訊。Thanks ~

    我這邊趕緊補上一些資訊:

    1.  硬體資訊如上回覆所示。

    2.  資料庫版本為Sql Server 2005 Standard (Version. 9.00.4035).

    3.  OSWindows Server 2003 Enterprise SP2 X86.

    4. 之前發生有依同事建議,先請客戶協助錄製下方Counter。

     

    項目

    最小值

    平均值

    最大值

    Server:Buffer Manager

    Buffer Cache hit ratio

    99.602

    99.823

    99.879

    SQL Server:Buffer Manager

    Free list stalls/sec

    0

    0

    0

    SQL Server:Buffer Manager

    Free pages

    305

    733

    1834

    SQL Server:Buffer Manager

    Lazy Writes/Sec

    0

    0.828

    10.968

    SQL Server:Buffer Manager

    Page Life Expectancy

    43

    49

    57

    SQLServer:Buffer Manager

    Page lookups/sec

    4256.83

    23151.219

    148746.12

    SQL Server:Buffer Manager

    Page reads/sec

    2925.25

    4066.45

    5207.57

    SQL Server:Buffer Manager

    Page writes/sec

    0

    244.737

    4868.41

    而另外您提到的動態管理檢視表、cpu進入低耗電狀態...等,方面資訊,這我要先花點時間研讀一下了。
    十分感謝版大您提供的方向,十分有助益。
    我會先研讀一下這幾個方向,感謝感謝。

    2010年7月9日 下午 01:01
  • Don't have to look at buffer counters, it's disk speed issue. What kind of disk the server has? Any issue in disk and related channel?
    2010年7月9日 下午 04:47
  • 根據 wait type 是 CXPacket 及其他的硬體描述,看來可能是平行處理的問題

    請試著下如下的指令處理看看

    sp_configure 'show advanced options', 1;
    GO
    reconfigure;
    GO


    sp_configure 'max degree of parallelism', 1;
    GO
    reconfigure;
    GO 


    請參考 http://soft.zdnet.com.cn/software_zone/2009/1218/1564615.shtml 及  http://www.cnblogs.com/rickie/archive/2008/03/18/1111965.html

     

    • 已標示為解答 Samwang 2010年7月12日 上午 05:33
    2010年7月9日 下午 10:53
  • Dear rmiao,

    十分感謝您的回覆,
    我會在彙整counters資訊,提供給客戶協助獲取相關資訊。
    另,硬碟部分是使用八顆(72 G 、2.5"、15000轉、SATA)硬碟。
    這部分有與客戶討論,但未明確請客戶確認各硬碟狀況,
    因目前是使用72G x 4 (RAID1)。這方面我再向客戶做確認。
    感謝您提供的資訊~~~Thanks~~

    2010年7月12日 上午 02:11
  • Dear Charles,

    再次感謝您的回覆~
    您提供的資訊相當明確~
    也有研讀了一下您提供的參考資訊。
    雖不太了解來龍去脈,可能的影響原因,
    但這部分我也會再提供給客戶,請客戶協助變更設定,看是否可順利解決問題。
    感謝感謝~~~

    2010年7月12日 上午 02:19
  • Dear Charles,

    十分感謝您提供的方向與資訊,
    在上午時間提供您的方法給客戶變更資料庫設定後,
    原Timeout問題便立刻可順利執行了!!

    Dear 前輩們,

    也感謝各位前輩提供的方法與資訊,
    都十分有助益!!
    還有須多東西我要花時間消化了~~

    這問題我也會持續在追蹤。

    感謝各位前輩的幫助! Thanks ~~~

    2010年7月12日 上午 05:33
  • Change server option means you disable parallelizm on the server, I prefer to find offending query and put maxdop = 1 in it.
    • 已標示為解答 Samwang 2010年7月13日 上午 01:35
    2010年7月12日 下午 02:15
  • Dear rmiao,

    感謝您的回覆與建議!
    這是個相當正確的建議!
    我會試著將此變更於程式中~
    並將server option 變更回來~

    並持續觀察~~
    Thanks a lot ~~~

    2010年7月13日 上午 01:35