淺談tempdb在SqlServer系統(tǒng)中的重要作用

簡(jiǎn)介：

       tempdb是SQLServer的系統(tǒng)數(shù)據(jù)庫一直都是SQLServer的重要組成部分，用來存儲(chǔ)臨時(shí)對(duì)象?？梢院?jiǎn)單理解tempdb是SQLServer的速寫板。應(yīng)用程序與數(shù)據(jù)庫都可以使用tempdb作為臨時(shí)的數(shù)據(jù)存儲(chǔ)區(qū)。一個(gè)實(shí)例的所有用戶都共享一個(gè)Tempdb。很明顯，這樣的設(shè)計(jì)不是很好。當(dāng)多個(gè)應(yīng)用程序的數(shù)據(jù)庫部署在同一臺(tái)服務(wù)器上的時(shí)候，應(yīng)用程序共享tempdb，如果開發(fā)人員不注意對(duì)Tempdb的使用就會(huì)造成這些數(shù)據(jù)庫相互影響從而影響應(yīng)用程序。

特性：

1、 tempdb中的任何數(shù)據(jù)在系統(tǒng)重新啟動(dòng)之后都不會(huì)持久存在。因?yàn)閷?shí)際上每次SQLServer啟動(dòng)的時(shí)候都會(huì)重新創(chuàng)建tempdb。這個(gè)特性就說明tempdb不需要恢復(fù)。

2、 tempdb始終設(shè)置為“simple”的恢復(fù)模式，當(dāng)你嘗試修改時(shí)都會(huì)報(bào)錯(cuò)。也就是說已提交事務(wù)的事務(wù)日志記錄在每個(gè)檢查點(diǎn)后都標(biāo)記為重用。

3、 tempdb也只能有一個(gè)filegroup，不能增加更多文件組。

4、 tempdb被用來存儲(chǔ)三種類型的對(duì)象：用戶對(duì)象，內(nèi)部對(duì)象、版本存儲(chǔ)區(qū)

以上是基礎(chǔ)知識(shí)。

如果SQL Server對(duì)tempdb訪問不頻繁,

tempdb對(duì)數(shù)據(jù)庫不會(huì)產(chǎn)生影響;相反如果訪問很頻繁,loading就會(huì)加重,

tempdb的性能就會(huì)對(duì)整個(gè)DB產(chǎn)生重要的影響

.優(yōu)化tempdb的性能變的很重要的,尤其對(duì)于大型數(shù)據(jù)庫。

注:在優(yōu)化tempdb之前,請(qǐng)先考慮tempdb對(duì)SQL Server性能產(chǎn)生多大的影響,評(píng)估遇到的問題以及可行性。

1、最小化的使用tempdb

SQL Server中很多的活動(dòng)都活發(fā)生在tempdb中,所以在某種情況可以減少多對(duì)tempdb的過度使用,以提高SQL Server的整體性能。

如下有幾處用到tempdb的地方：

(1)用戶建立的臨時(shí)表.如果能夠避免不用,就盡量避免. 如果使用臨時(shí)表儲(chǔ)存大量的數(shù)據(jù)且頻繁訪問,考慮添加index以增加查詢效率。

(2)Schedule jobs.如DBCC CHECKDB會(huì)占用系統(tǒng)較多的資源,較多的使用tempdb.最好在SQL Server loading比較輕的時(shí)候做。

(3)Cursors.游標(biāo)會(huì)嚴(yán)重影響性能應(yīng)當(dāng)盡量避免使用。

(4)CTE(Common Table Expression).也會(huì)在tempdb中執(zhí)行。

(5)SORT_INT_TEMPDB.建立index時(shí)會(huì)有此選項(xiàng)。

(6)Index online rebuild。

(7)臨時(shí)工作表及中間結(jié)果集.如JOIN時(shí)產(chǎn)生的。

(8)排序的結(jié)果。

(9)AFTER and INSTEAD OF triggers。

不可能避免使用tempdb,如果有tempdb的瓶頸或issue,就該返回來考慮這些問題了。

2、重新分配tempdb的空間大小

在SQL Server重啟時(shí)會(huì)自動(dòng)建立8MB大小的tempdb,自動(dòng)增長(zhǎng)默認(rèn)為10%. 對(duì)于小型的數(shù)據(jù)庫來說,8MB大小已經(jīng)足夠了.但是對(duì)于較大型的數(shù)據(jù)庫來說,8MB遠(yuǎn)遠(yuǎn)不能滿足SQL Server頻繁活動(dòng)的需要,因此會(huì)按照10%的比例增加,比如說需要1GB,則會(huì)需要較長(zhǎng)的時(shí)間,此段時(shí)間會(huì)嚴(yán)重影響SQL Server的性能. 建議在SQL Server啟動(dòng)時(shí)設(shè)置tempdb的初始化的大小(如下圖片設(shè)置為MDF:300MB,LDF:50MB),也可以通過ALTER DATABASE來實(shí)現(xiàn). 這樣在SQL Server在重啟時(shí)tempdb就會(huì)有足夠多的空間可利用,從而提高效率。

難點(diǎn)在于找到合理的初始化大小,在SQL Server活動(dòng)頻繁且tempdb不在增長(zhǎng)時(shí)會(huì)是一個(gè)合適的值,可以設(shè)置此時(shí)的值為Initial Size;當(dāng)然還會(huì)有更多的考量,此為一例。

3、不要收縮tempdb(如沒有必要)

有時(shí)候我們會(huì)注意到tempdb占用很大的空間,但是可用的空間會(huì)比較低時(shí),會(huì)想到shrink數(shù)據(jù)庫來釋放磁盤空間, 此時(shí)要小心了,可能會(huì)影響到性能。

4 、分派tempdb的文件和其他數(shù)據(jù)文件到不用的IO上

tempdb對(duì)IO的要求比較高,最好分配到高IO的磁盤上且與其他的數(shù)據(jù)文件分到不用的磁盤上,以提高讀寫效率.

tempdb也分成多個(gè)文件,一般會(huì)根據(jù)CPU來分,幾個(gè)CPU就分幾個(gè)tempdb的數(shù)據(jù)文件. 多個(gè)tempdb文件可以提高讀寫效率并且減少IO活動(dòng)的沖突。

常用的腳本如下：

--查tempdb庫的大小

select a.name, sum(size)*8.0/1024/1024 as [GB]

, sum(size)*8.0/1024 as [MB]

from master.dbo.sysdatabases a inner join master.dbo.sysaltfiles b on a.dbid=b.dbid

WHERE a.name='tempdb'

group by a.name

order by 1

后記：

tempdb是SQL Server重要的一部分,今天發(fā)現(xiàn)數(shù)據(jù)的tempdb特別大,所以對(duì)tempdb的一些了解和總結(jié),以后還需要進(jìn)一步學(xué)習(xí)。

您可能感興趣的文章:

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com