RiCo技術農場

上禮拜收到一封求救Mail，

開發人員說執行網頁應用程式(不管select、delete、update、insert)

常常會出現timeout(早上都沒問題，但大都在下午3點開始發生)，

然後就無法連到資料庫。

我以往經驗應該都是某些不良SQL或SP造成(這次確實也是)

但這案例我也發現tempdb問題也滿大的，

因為前端應用程式大量使用temptable、資料表變數、cursor(了解應用程式特性也是相當重要的)，

而且某幾支SP存在deadlock和無窮迴圈(心中OS: e04...是在寫燒機程式喔)，

造成tempdb size異常快速增加(吃光硬碟空間、無法連到資料庫)且擴張空間耗費過久(造成前端timeout)

我請相關人員處理那些不良的SQL和SP，

同時我也對tempdb做了以下優化。

 

當時我處理的tempdb大小約143GB

 

1.tempdb初始大小應符合應用程式特性設定合適大小。

資料庫檔案初始化可改善效能，也可降低碎片量。

 

2.tempdb自動成長大小應符合應用程式特性設定合適大小。

如果該值太小(成長比寫入的量小)，將造成過多的擴大處理和碎片而影響效能。

如果該值太大可能會造成 Timeout(等待擴充)。

所以需要符合應用程式取得一各平衡。

 

3.使用多個資料檔案(依CPU個數來建立)。

減少tempdb的資源競爭。

 

4.區隔tempdb和使用者資料庫檔案所使用硬碟(tempdb可以存放在RAID 0，如果可以的話)。

增加I/O效能，減少資源競爭。

 

5.保留硬碟一定空間

每顆硬碟應該保留5~10%空間留給NTFS演算法運作，不建議完全用光。

 

6.tempdb復原模式請設定簡單

下面是我切4個檔案測試4次select效能

--single datafile
--1 CPU 時間= 251 ms，經過時間= 1917 ms 
--2 CPU 時間= 360 ms，經過時間= 2057 ms 
--3 CPU 時間= 219 ms，經過時間= 1708 ms
--4 CPU 時間= 312 ms，經過時間= 2102 ms
--AVG CPU 時間=285.5 ms ，經過時間=1946 ms

--multiple datafile
--1 CPU 時間= 218 ms，經過時間= 1607 ms
--2 CPU 時間= 203 ms，經過時間= 1435 ms
--3 CPU 時間= 202 ms，經過時間= 1328 ms
--4 CPU 時間= 189 ms，經過時間= 1217 ms
--AVG CPU 時間=203 ms ，經過時間=1396.75ms

 

參考

最佳化 tempdb 效能

最佳化資料庫

資料庫檔案初始化