步驟1.TempDB壓力診斷

等待類型診斷

TempDB的爭(zhēng)用壓力在等待篇中已經(jīng)簡(jiǎn)單介紹，等待的表現(xiàn)為 pagelatch_類等待，等待的資源是 “2： X ：X ”

tempDB所在磁盤的響應(yīng)時(shí)間

一個(gè)實(shí)例下只有一個(gè)tempdb，也就是當(dāng)你在一個(gè)實(shí)例下創(chuàng)建了100個(gè)數(shù)據(jù)庫，這100個(gè)數(shù)據(jù)庫也只能用這一個(gè)TempDB。

你創(chuàng)建的臨時(shí)表，或SQL執(zhí)行語句所需要的排序等操作都需要用到Tempdb。所以TempDB對(duì)磁盤的響應(yīng)時(shí)間要求比較高。

步驟2.解決問題

把TempDB設(shè)置成多個(gè)來分?jǐn)傔@個(gè)壓力。

分成多個(gè)文件

　　　　作為一般規(guī)則，如果邏輯處理器數(shù)小于或等于 8，使用和邏輯處理器相同數(shù)量的數(shù)據(jù)文件。如果邏輯處理器數(shù)大于 8 時(shí)，使用 8 個(gè)數(shù)據(jù)文件，然后如果仍然存在爭(zhēng)用，增加數(shù)據(jù)文件數(shù)4 的倍數(shù)（最多的邏輯處理器數(shù)）直到爭(zhēng)用降低到可接受的程度或?qū)ぷ髫?fù)荷/代碼進(jìn)行更改。

文件大小、增長(zhǎng)率要相同

　　　這里需要注意一個(gè)小細(xì)節(jié)，你所分配的文件必須大小一致，如果設(shè)置自動(dòng)增長(zhǎng)那么增長(zhǎng)率要相同。

TempDB磁盤劃分

　　大多數(shù)情況下，TempDB的文件不需要拆分磁盤，在同一個(gè)磁盤即可，如果壓力大可以選擇放置在一個(gè)單獨(dú)的磁盤中，這樣不會(huì)與其他文件（如數(shù)據(jù)讀寫）發(fā)生磁盤資源競(jìng)爭(zhēng)。

　　　　如果出現(xiàn)TempDB 讀取響應(yīng)時(shí)間高的情況，請(qǐng)考慮，TempDB的磁盤相關(guān)優(yōu)化，如將TempDB文件單獨(dú)放入比較快的磁盤。

步驟3.語句調(diào)優(yōu)

　　語句調(diào)優(yōu)篇提到語句中使用臨時(shí)表或表變等會(huì)減少語句的復(fù)雜度，提升語句的效率，是常用的三板斧之一，但這里的需要一個(gè)平衡。如果對(duì)語句過度使用會(huì)造成文中提到的TempDB壓力。那么怎么樣平衡呢？下面給出幾點(diǎn)建議：

切記不要過度使用臨時(shí)表！臨時(shí)表的使用主要有兩個(gè)場(chǎng)景，拆分語句降低復(fù)雜性。另一個(gè)是緩存中間結(jié)果避免重復(fù)操作。
減少使用臨時(shí)表鎖系統(tǒng)表的時(shí)間！”select 字段 into #臨時(shí)表 from“ 如果語句執(zhí)行時(shí)間過長(zhǎng)這將是災(zāi)難，盡量選用先創(chuàng)建，后插入的做法。

原理：TempDB壓力從哪來？

　　　　當(dāng)數(shù)據(jù)庫創(chuàng)建一張新表的時(shí)候，SQL Server要為這張表分配存儲(chǔ)頁面，同時(shí)SQL Server也要修改SGAM, PFS, 和GAM頁面，把已經(jīng)分配出去的頁面標(biāo)志成已使用。所以每創(chuàng)建一張新表，SGAM, PFS, 和GAM這些系統(tǒng)頁面都會(huì)有修改動(dòng)作。這種行為對(duì)一般的用戶數(shù)據(jù)庫不會(huì)有問題，因?yàn)檎５膽?yīng)用不會(huì)折騰著不停地建表、刪表。但是tempdb就不同了。如果一個(gè)存儲(chǔ)過程使用了臨時(shí)表，而這個(gè)存儲(chǔ)過程被并發(fā)用戶廣泛使用，那很自然地就會(huì)有很多并發(fā)用戶在tempdb里同時(shí)創(chuàng)建表，做完了以后又刪除表。這樣，在一個(gè)時(shí)間點(diǎn)，會(huì)有很多任務(wù)要修改SGAM, PFS, 或GAM頁面。但是為了維護(hù)物理的一致性，對(duì)于同一個(gè)頁面，SQL Server在一個(gè)時(shí)間點(diǎn)同時(shí)只允許一個(gè)用戶修改它。所以對(duì)于tempdb，如果同時(shí)有很多很多人要在同一個(gè)數(shù)據(jù)文件里分配空間，那這個(gè)數(shù)據(jù)文件的SGAM, PFS, 或GAM頁面，就有可能成為系統(tǒng)瓶頸。大家只能一個(gè)一個(gè)做，并發(fā)度上不去。

　　　　這就好像你進(jìn)停車場(chǎng)要登記交費(fèi)一樣！一個(gè)一個(gè)來不要急~

　　　　等待資源為： “2：1：3” 這是什么意思？ ID 為 2 的數(shù)據(jù)庫（TempDB）的 1號(hào)文件的頁碼為3的頁（SGAM頁面）！

　　　　這里關(guān)于系統(tǒng)頁不過多的介紹，想詳細(xì)了解的朋友請(qǐng)參見： SQL Server中的GAM頁和SGAM頁

我創(chuàng)建個(gè)臨時(shí)表跟系統(tǒng)頁還有關(guān)系？

　　　　下面也用一個(gè)例子說明：

　　　　創(chuàng)建臨時(shí)表的時(shí)候會(huì)對(duì)系統(tǒng)表中進(jìn)行插入和更新，而刪除臨時(shí)表逆向過程會(huì)刪除或更新系統(tǒng)表！

use [AdventureWorks2012]GOcheckpointgocreate table #t(id int)drop table #tuse tempdbgoselect Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)