SQL Server誤區(qū)30日談 第12天 TempDB的文件數(shù)和需要和CPU數(shù)目保持一致

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

錯(cuò)誤

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

    但讓人困惑的是SQL CAT團(tuán)隊(duì)給出的建議就是1：1，但這個(gè)建議是源自擴(kuò)展方面的原理來(lái)說(shuō),而不是一個(gè)通用法則。因?yàn)樗麄兯鎸?duì)的大型客戶數(shù)據(jù)量服務(wù)器和IO子系統(tǒng)都是大部分人沒(méi)有機(jī)會(huì)遇到的。

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

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

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

    如果你遇到閂鎖爭(zhēng)用，可以通過(guò)跟蹤標(biāo)記1118或是多建一個(gè)TempDB文件來(lái)緩和這個(gè)狀況(原理可以在知識(shí)庫(kù)KB 328551查到),我已經(jīng)寫(xiě)了一篇關(guān)于為什么追蹤標(biāo)記1118依然被需要的長(zhǎng)博文，鏈接：Misconceptions around TF 1118。

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

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

    不過(guò)這也不能一概而論。上周我遇到一個(gè)問(wèn)題，一個(gè)客戶的TempDB負(fù)載大到需要32個(gè)CPU配上64個(gè)TempDB文件才能減輕閂鎖爭(zhēng)用。這是否意味著這是一個(gè)最佳實(shí)踐呢？當(dāng)然不是。

    那你或許有疑問(wèn)，為什么1:1的比例不好呢，那是因?yàn)樘嗟腡empDB有可能引起另一個(gè)性能問(wèn)題。如果你的一條查詢中某些操作（比如排序）需要使用大量的內(nèi)存，但內(nèi)存不夠時(shí)，就需要將這些內(nèi)容分配到TempDB中。當(dāng)存在多個(gè)TempDB文件時(shí),由于TempDB的循環(huán)分配機(jī)制,這有可能導(dǎo)致性能被拖累，對(duì)于比較大的臨時(shí)表也是如此。

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

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

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

    希望上面的指導(dǎo)方針對(duì)你有幫助。

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