[SQL SERVER][Memo] tempdb datafile該切多少份?

2013-06-20

看到 SQL PASS TW 大家在討論該不該切割 tempdb datafile問題?

我以往都遵守 MS 官方建議依照CPU core 數量切割相對 tempdb datafile 數量(1:1)，

但是不是一定要切多個data file 並遵守 1:1 原則呢?

我個人覺得沒有一定的答案，大多還是要看資料庫workload特性而定，

如同我之前遇到的問題，前端程式大量使用tempdb table 、資料表變數、cursor、hash 、排序或群組大量資料..等，

這時查看wait type大部分都是PAGELATCH or PAGEIOLATCH 類型，

透過切多個 tempdb datafile來減少資源競爭問題是有效的，

不過我曾經也踩過切多個tempdb datafile所造成另一個效能問題(1:2 或 1:4 我都有切過....XD)，

因為前端大量使用tempdb，且SQL Server 存在記憶體壓力時，

這時LazyWrite會去釋放buffersPool空間，

因為記憶體不足，所以相關物件會存放在tempdb中，

你要知道SQL Server處理dafafile是採取隨機讀寫的(log file 採取循序寫入)，

如果tempdb datafile 數量有8個、16個、32個、64個、甚至切到128個時，

隨機寫入這麼多個檔案並同步處理的額外成本也不可忽視，

如果這些檔案大小又都不相同的話，也會造成寫入不平均(導致某個檔案過大，熱點很容易產生)，

可想而知I/O效能問題馬上浮現(隨機讀寫真是I/O系統一大殺手)，

所以現在當我要增加 tempdb datafile 時，我不會像以前那麼豪邁隨興......

很感謝下面兩篇技術文章，讓我知道SQL Server世界是博大精深的

參考

RiCo技術農場