步驟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ù)庫(kù)，這100個(gè)數(shù)據(jù)庫(kù)也只能用這一個(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)建議：
原理：TempDB壓力從哪來？當(dāng)數(shù)據(jù)庫(kù)創(chuàng)建一張新表的時(shí)候，SQL Server要為這張表分配存儲(chǔ)頁(yè)面，同時(shí)SQL Server也要修改SGAM, PFS, 和GAM頁(yè)面，把已經(jīng)分配出去的頁(yè)面標(biāo)志成已使用。所以每創(chuàng)建一張新表，SGAM, PFS, 和GAM這些系統(tǒng)頁(yè)面都會(huì)有修改動(dòng)作。這種行為對(duì)一般的用戶數(shù)據(jù)庫(kù)不會(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頁(yè)面。但是為了維護(hù)物理的一致性，對(duì)于同一個(gè)頁(yè)面，SQL Server在一個(gè)時(shí)間點(diǎn)同時(shí)只允許一個(gè)用戶修改它。所以對(duì)于tempdb，如果同時(shí)有很多很多人要在同一個(gè)數(shù)據(jù)文件里分配空間，那這個(gè)數(shù)據(jù)文件的SGAM, PFS, 或GAM頁(yè)面，就有可能成為系統(tǒng)瓶頸。大家只能一個(gè)一個(gè)做，并發(fā)度上不去。 這就好像你進(jìn)停車場(chǎng)要登記交費(fèi)一樣！一個(gè)一個(gè)來不要急~
等待資源為 ： “2：1：3” 這是什么意思？ ID 為 2 的數(shù)據(jù)庫(kù)（TempDB）的 1號(hào)文件 的 頁(yè)碼為3的頁(yè)（SGAM頁(yè)面）！
這里關(guān)于系統(tǒng)頁(yè)不過多的介紹，想詳細(xì)了解的朋友請(qǐng)參見 ： SQL Server中的GAM頁(yè)和SGAM頁(yè)
我創(chuàng)建個(gè)臨時(shí)表跟系統(tǒng)頁(yè)還有關(guān)系？下面也用一個(gè)例子說明 ： 創(chuàng)建臨時(shí)表的時(shí)候會(huì)對(duì)系統(tǒng)表中進(jìn)行插入和更新，而刪除臨時(shí)表逆向過程會(huì)刪除或更新系統(tǒng)表！  use [AdventureWorks2012] GO checkpoint go create table #t ( id int ) drop table #t use tempdb go select Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)
所以當(dāng)你并發(fā)過高且頻繁創(chuàng)建刪除臨時(shí)表的時(shí)候就會(huì)造成大量的爭(zhēng)用。
|
|
|
