資料庫實體檔案，成長大小設定錯誤，造成之系統瓶頸排除

遇到一個系統上的問題，將此次請教公司資深同事，一起將問題排除的整個過程做簡單記錄!

發生問題

於2010/04/17 20:00左右，系統某轉檔程式，會一直在同一個Query上發生資料庫TimeOut的問題，

但當我將此Query拿至SSMS執行，卻又相當正常(已執行ClearBuffer)。

 

問題排除整體分兩階段

[Log查詢，Performance Counter記錄，問題初步判斷]

1. 先看到最初發生系統錯誤Log於2010/04/17 20:00，錯誤訊息為對XXX資料表的Query發生TimeOut。
2. 接著查看各系統Log(專案系統Log、EventLog、SqlServerLog)，而於Sql Server Log中，也於同上時間點，開始發生如下訊息。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. 於是先記錄Performance Counter 確認系統Avg. Disk Queue的平均值約停留於37上下,確實比建議值<2高過許多!
4. 接著試著找出造成Disk瓶頸的原因，接著錄下系統SqlServer相關Counter，發現記憶體已完全被使用完，且Counter數據也與建議值差異甚大.因此初步判斷是記憶體不足，所照成磁碟不斷寫入，進而有磁碟上的瓶頸。

[資料庫設定，問題排除]

1. 接著往DB設定上查詢。
2. 同事發現資料庫存放XXX資料表的實體檔，當實體檔不足時，每次居然使用預設值每次只擴充成長1MB的大小。(此XXX資料表目前已成長到約35G的大小，因當初規劃就知道此資料檔相當龐大，又讀寫頻繁，所以當初有刻意將此資料表規劃獨立在一個資料庫實體檔中，因此此35G大小的實體檔，其實只存放了一個此XXX資料表)
3. 同事認為以上設定大有問題，有可能會造成不斷在重新配置大小，也就有可能造成磁碟上的讀寫頻繁!因此先將此設定改為一次成長100MB。
4. 最後在無意間再次查詢Performance Counter時，發現磁碟效能瓶頸問題完全解除!系統程式也不再發生TimeOut! 再比對回覆正常的時間，與設定一次成長100MB的時間點發現相吻合。
5. 以此確認為預設1MB的設定，造成了磁碟I/O上不斷需要被重新配置。

以上排除問題方式我想有一定的方向可循，雖其中也還有許多疑點可循值得探討，可覺得還是一個不錯的錯誤排除記錄，可供往後繼續參考!