了解tempdb對(duì)SQL Server數(shù)據(jù)庫(kù)性能的影響
本文是提升SQL Server數(shù)據(jù)庫(kù)性能方面的討論，51CTO數(shù)據(jù)庫(kù)頻道向您推薦《SQL Server入門(mén)到精通》以便于您更深刻的了解SQL Server數(shù)據(jù)庫(kù)。
1.SQL Server系統(tǒng)數(shù)據(jù)庫(kù)介紹
SQL Server有四個(gè)重要的系統(tǒng)級(jí)數(shù)據(jù)庫(kù):master,model,msdb,tempdb.
master:記錄SQL Server系統(tǒng)的所有系統(tǒng)級(jí)信息,包括實(shí)例范圍的元數(shù)據(jù),端點(diǎn),鏈接服務(wù)器和系統(tǒng)配置設(shè)置,還記錄其他數(shù)據(jù)庫(kù)是否存在以及這些數(shù)據(jù)問(wèn)文件的位置等等.如果master不可用,數(shù)據(jù)庫(kù)將不能啟動(dòng).
model:用在SQL Server 實(shí)例上創(chuàng)建的所有數(shù)據(jù)庫(kù)的模板。因?yàn)槊看螁?dòng) SQL Server 時(shí)都會(huì)創(chuàng)建 tempdb，所以 model 數(shù)據(jù)庫(kù)必須始終存在于 SQL Server 系統(tǒng)中。
msdb:由SQL Server 代理用來(lái)計(jì)劃警報(bào)和作業(yè)。
tempdb:是連接到 SQL Server 實(shí)例的所有用戶都可用的全局資源，它保存所有臨時(shí)表,臨時(shí)工作表,臨時(shí)存儲(chǔ)過(guò)程,臨時(shí)存儲(chǔ)大的類(lèi)型,中間結(jié)果集,表變量和游標(biāo)等。另外，它還用來(lái)滿足所有其他臨時(shí)存儲(chǔ)要求.
2.tempdb內(nèi)在運(yùn)行原理
與其他SQL Server數(shù)據(jù)庫(kù)不同的是,tempdb在SQL Server停掉,重啟時(shí)會(huì)自動(dòng)的drop,re-create. 根據(jù)model數(shù)據(jù)庫(kù)會(huì)默認(rèn)建立一個(gè)新的8MB(mdf file:8MB;ldf file:1MB, autogtouth設(shè)置為10%)大小recovery model為simple的tempdb數(shù)據(jù)庫(kù).
tempdb數(shù)據(jù)庫(kù)建立之后,DBA可以在其他的數(shù)據(jù)庫(kù)中建立數(shù)據(jù)對(duì)象,臨時(shí)表,臨時(shí)存儲(chǔ)過(guò)程,表變量等會(huì)加到tempdb中.在tempdb活動(dòng)很頻繁時(shí),能夠自動(dòng)的增長(zhǎng),因?yàn)槭莝imple的recovery model,會(huì)最小化日志記錄,日志也會(huì)不斷的截?cái)?
3.如何合理的優(yōu)化tempdb以提高SQL Server的性能
如果SQL Server對(duì)tempdb訪問(wèn)不頻繁,tempdb對(duì)數(shù)據(jù)庫(kù)不會(huì)產(chǎn)生影響;相反如果訪問(wèn)很頻繁,loading就會(huì)加重,tempdb的性能就會(huì)對(duì)整個(gè)DB產(chǎn)生重要的影響.優(yōu)化tempdb的性能變的很重要的,尤其對(duì)于大型數(shù)據(jù)庫(kù).
注:在優(yōu)化tempdb之前,請(qǐng)先考慮tempdb對(duì)SQL Server性能產(chǎn)生多大的影響,評(píng)估遇到的問(wèn)題以及可行性.
3.1最小化的使用tempdb
SQL Server中很多的活動(dòng)都活發(fā)生在tempdb中,所以在某種情況可以減少多對(duì)tempdb的過(guò)度使用,以提高SQL Server的整體性能.
如下有幾處用到tempdb的地方:
(1)用戶建立的臨時(shí)表.如果能夠避免不用,就盡量避免. 如果使用臨時(shí)表儲(chǔ)存大量的數(shù)據(jù)且頻繁訪問(wèn),考慮添加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,就該返回來(lái)考慮這些問(wèn)題了.
3.2重新分配tempdb的空間大小
在SQL Server重啟時(shí)會(huì)自動(dòng)建立8MB大小的tempdb,自動(dòng)增長(zhǎng)默認(rèn)為10%. 對(duì)于小型的數(shù)據(jù)庫(kù)來(lái)說(shuō),8MB大小已經(jīng)足夠了.但是對(duì)于較大型的數(shù)據(jù)庫(kù)來(lái)說(shuō),8MB遠(yuǎn)遠(yuǎn)不能滿足SQL Server頻繁活動(dòng)的需要,因此會(huì)按照10%的比例增加,比如說(shuō)需要1GB,則會(huì)需要較長(zhǎng)的時(shí)間,此段時(shí)間會(huì)嚴(yán)重影響SQL Server的性能. 建議在SQL Server啟動(dòng)時(shí)設(shè)置tempdb的初始化的大小(如下圖片設(shè)置為MDF:300MB,LDF:50MB),也可以通過(guò)ALTER DATABASE來(lái)實(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.3不要收縮tempdb(如沒(méi)有必要)
有時(shí)候我們會(huì)注意到tempdb占用很大的空間,但是可用的空間會(huì)比較低時(shí),會(huì)想到shrink數(shù)據(jù)庫(kù)來(lái)釋放磁盤(pán)空間, 此時(shí)要小心了,可能會(huì)影響到性能.
如上圖所示:tempdb分配的空間為879.44MB,有45%的空間是空閑的,如果shrink掉,可以釋放掉一部分磁盤(pán)空閑,但是之后SQL Server如有大量的操作時(shí),tempdb空間不夠用,又會(huì)按照10%的比例自動(dòng)增長(zhǎng). 這樣子的話,所做的shrink操作是無(wú)效的,還會(huì)增加系統(tǒng)的loading.
3.4 分派tempdb的文件和其他數(shù)據(jù)文件到不用的IO上
tempdb對(duì)IO的要求比較高,最好分配到高IO的磁盤(pán)上且與其他的數(shù)據(jù)文件分到不用的磁盤(pán)上,以提高讀寫(xiě)效率.
tempdb也分成多個(gè)文件,一般會(huì)根據(jù)CPU來(lái)分,幾個(gè)CPU就分幾個(gè)tempdb的數(shù)據(jù)文件. 多個(gè)tempdb文件可以提高讀寫(xiě)效率并且減少I(mǎi)O活動(dòng)的沖突.
tempdb是SQL Server重要的一部分,以上只是對(duì)tempdb的一些了解總結(jié),還需要進(jìn)一步學(xué)習(xí)...
原文標(biāo)題：tempdb對(duì)SQL Server性能的影響
鏈接：http://www.cnblogs.com/changbluesky/archive/2010/04/15/1711733.html
【編輯推薦】
