SQL Server誤區30日談 第12天 TempDB的檔案數和需要和CPU數目保持一致

誤區 #12:TempDB的檔案數和需要和CPU數目保持一致

錯誤

哎，由於上述誤區是微軟“官方”的建議，並且還有大量博文堅持這個觀點，這個誤區已經是老生常談。

但讓人困惑的是SQL CAT團隊給出的建議就是1：1，但這個建議是源自擴充方面的原理來說,而不是一個通用法則。因為他們所面對的大型客戶資料量伺服器和IO子系統都是大部分人沒有機會遇到的。

每個執行個體僅僅允許有一個TempDb,但需要用到TempDB的地方卻有很多，所以TempDB很容易成為效能瓶頸，我想大家數人都瞭解這一點，而大多數人所不瞭解的應該是在什麼情況下才需要額外的TempDB檔案。

當你看到PAGELATCH類型的阻塞時，說明遇到記憶體中分配位元影像的爭用問題了。而看到PAGEIOLATCH，說明遇到I/O子系統層面的爭用問題了。對於閂鎖（Latch）你可以將其看作和普通鎖是一種東西，但更輕量,更短，並且只會被儲存引擎內部使用。

MVP Glenn Berry 有一篇博文裡有查看sys.dm_os_wait_stats的DMV。這篇博文中可以查到你的伺服器造成阻塞最多的原因是什麼。如果你發現是PAGELATCH型等待，你可以使用這段指令碼來查看是由於FPS,GAM還是SGAM爭用造成的問題。

如果你遇到閂鎖爭用，可以通過跟蹤標記1118或是多建一個TempDB檔案來緩和這個狀況(原理可以在知識庫KB 328551查到),我已經寫了一篇關於為什麼追蹤標記1118依然被需要的長博文，連結：Misconceptions around TF 1118。

在SQL SERVER 2000時代，TempDB的檔案數需要和CPU核心數保持1：1的關係，在SQL SERVER 2005和2008版本這條建議也適用，但由於SQL SERVER 2005+後的最佳化措施(詳細請看我的博文),你不再需要嚴格按照1：1的比例關係設定CPU核心數和TempDB檔案數，而是檔案數和CPU核心數的比例保持在1:2或是1:4就行了。

[題外話：在SQL PASS 2011我的好朋友Bob Ward,也是SQL CSS最牛的人。給出了一個新的公式:如果CPU核心數小於等於8，使其比例保持在1:1,而如果CPU核心數大於8，使用8個檔案，當你發現閂鎖爭用現象時，每次額外加4個檔案]

不過這也不能一概而論。上周我遇到一個問題，一個客戶的TempDB負載大到需要32個CPU配上64個TempDB檔案才能減輕閂鎖爭用。這是否意味著這是一個最佳實務呢？當然不是。

那你或許有疑問，為什麼1:1的比例不好呢，那是因為太多的TempDB有可能引起另一個效能問題。如果你的一條查詢中某些操作（比如排序）需要使用大量的記憶體，但記憶體不夠時，就需要將這些內容分配到TempDB中。當存在多個TempDB檔案時,由於TempDB的迴圈分配機制,這有可能導致效能被拖累，對於比較大的暫存資料表也是如此。

那為什麼迴圈分配機制對於TempDB存在大量檔案時產生效能問題呢？有如下幾種可能:

• 迴圈分配演算法是針對檔案組而言，而對於TempDB只能存在一個檔案組。當這個檔案組包含16或32個檔案時，由於迴圈分配演算法的線程有限，但對於大量檔案的TempDB依然需要做一些額外的同步工作，因此這部分工作會造成效能損失
• TempDB的檔案大小不一致，則有可能導致某個單獨檔案的自動成長，從而造成熱點IO。
• 當緩衝區需要通過LazyWriter釋放一些空間時(TempDB的Checkpoint不會做寫回操作)，多個TempDB檔案有可能導致IO子系統的隨機讀寫問題，這會導致IO方面的效能問題。

所以這個選擇讓你進亦憂，退亦憂。到底多少TempDB檔案才是合適的呢？我也不能給你具體答案，但是基於我多年諮詢經驗以及出席各種大會的經驗，我可以給你一個指導方針---當為瞭解決閂鎖爭用時為TempDB建立多個檔案要小心，僅僅在必須情況下才額外增加TempDB檔案。也就是你需要在可擴充性和效能之間取得一個平衡。

希望上面的指導方針對你有協助。

PS:回應一些評論：TempDB的檔案沒有必要分布在多個儲存空間之間。如果你看到PAGELATCH類型的等待，即使你進行了分布也不會改善效能，而如果PAGEIOLATCH型的等待，或許你需要多個儲存空間，但這也不是必然-有可能你需要講整個TempDB遷移到另一個儲存系統，而不是僅僅為TempDB增加一個檔案。這需要你仔細分析後再做定奪。