誤區(qū) #12:TempDB的文件數(shù)和需要和CPU數(shù)目保持一致

錯誤

    哎，由于上述誤區(qū)是微軟“官方”的建議，并且還有大量博文堅持這個觀點，這個誤區(qū)已經(jīng)是老生常談。

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

    每個實例僅僅允許有一個TempDb,但需要用到TempDB的地方卻有很多，所以TempDB很容易成為性能瓶頸，我想大家數(shù)人都了解這一點，而大多數(shù)人所不了解的應該是在什么情況下才需要額外的TempDB文件。

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

    MVP Glenn Berry 有一篇博文里有查看sys.dm_os_wait_stats的DMV。這篇博文中可以查到你的服務器造成阻塞最多的原因是什么。如果你發(fā)現(xiàn)是PAGELATCH型等待，你可以使用這段腳本來查看是由于FPS,GAM還是SGAM爭用造成的問題。

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

    在SQL SERVER 2000時代，TempDB的文件數(shù)需要和CPU核數(shù)保持1：1的關系，在SQL SERVER 2005和2008版本這條建議也適用，但由于SQL SERVER 2005+后的優(yōu)化措施(詳細請看我的博文),你不再需要嚴格按照1：1的比例關系設置CPU核數(shù)和TempDB文件數(shù)，而是文件數(shù)和CPU核數(shù)的比例保持在1:2或是1:4就行了。

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

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

    那你或許有疑問，為什么1:1的比例不好呢，那是因為太多的TempDB有可能引起另一個性能問題。如果你的一條查詢中某些操作（比如排序）需要使用大量的內存，但內存不夠時，就需要將這些內容分配到TempDB中。當存在多個TempDB文件時,由于TempDB的循環(huán)分配機制,這有可能導致性能被拖累，對于比較大的臨時表也是如此。

    那為什么循環(huán)分配機制對于TempDB存在大量文件時產生性能問題呢？有如下幾種可能:

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

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

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

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