有關Tempdb資料庫大小問題

1.SQL Server在每次重啟服務時會重建tempdb資料庫

 如果系統運行過程中 tempdb因需要自動成長了，SQL Serve不會記住增長後的大小，重啟服務後仍然恢複到初始大小，但如果使用者使用了手工調整tempdb的大小，重啟服務SQL Server會把tempdb重建為使用者指定大小 測試樣本 tempdb初始化大小為8MB 1)使tempdb自動成長select b.* into #t from sysprocesses a,sysobjects b重啟後使用sp_helpdb 'tempdb'可以看到tempdb又恢複到8MB2) 使用者使用Alter Database調整為100MB, USE masterGOALTER DATABASE tempdbMODIFY FILE   (NAME = tempdev,SIZE = 100MB) 重啟服務後使用查看tempdb大小就為100MB 以下查詢可以看到tempdb的變化select a.filename,a.name,a.size*8.0/1024.0 as originalsize_MB,f.size*8.0/1024.0 as currentsize_MBfrom master..sysaltfiles a join tempdb..sysfiles f on a.fileid=f.fileidwhere dbid=db_id('tempdb')and a.size<>f.size 總結:當系統自動調整tempdb大小時，對檔案的讀寫將暫時的阻塞所以如果我們預知tempdb將會增加到某個大小時，可以自行調整，從而避免效能下降 

2.tempdb內在運行原理

與其他SQL Server資料庫不同的是,tempdb在SQL Server停掉,重啟時會自動的drop,re-create. 根據model資料庫會預設建立一個新的8MB(mdf file:8MB;ldf file:1MB, autogtouth設定為10%)大小recovery model為simple的tempdb資料庫.

tempdb資料庫建立之後,DBA可以在其他的資料庫中建立資料對象,暫存資料表,暫存預存程序,表變數等會加到tempdb中.在tempdb活動很頻繁時,能夠自動的增長,因為是simple的recovery model,會最小化日誌記錄,日誌也會不斷的截斷.

3.如何合理的最佳化tempdb以提高SQL Server的效能

如果SQL Server對tempdb訪問不頻繁,tempdb對資料庫不會產生影響;相反如果訪問很頻繁,loading就會加重,tempdb的效能就會對整個DB產生重要的影響.最佳化tempdb的效能變的很重要的,尤其對於大型資料庫.

注:在最佳化tempdb之前,請先考慮tempdb對SQL Server效能產生多大的影響,評估遇到的問題以及可行性.

3.1最小化的使用tempdb

SQL Server中很多的活動都活發生在tempdb中,所以在某種情況可以減少多對tempdb的過度使用,以提高SQL Server的整體效能.

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

(1)使用者建立的暫存資料表.如果能夠避免不用,就盡量避免. 如果使用暫存資料表儲存大量的資料且頻繁訪問,考慮添加index以增加查詢效率.

(2)Schedule jobs.如DBCC CHECKDB會佔用系統較多的資源,較多的使用tempdb.最好在SQL Server loading比較輕的時候做.

(3)Cursors.遊標會嚴重影響效能應當盡量避免使用.

(4)CTE(Common Table Expression).也會在tempdb中執行.

(5)SORT_INT_TEMPDB.建立index時會有此選項.

(6)Index online rebuild.

(7)臨時工作表及中間結果集.如JOIN時產生的.

(8)排序的結果.

(9)AFTER and INSTEAD OF triggers.

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

3.2重新分配tempdb的空間大小

在SQL Server重啟時會自動建立8MB大小的tempdb,自動成長預設為10%. 對於小型的資料庫來說,8MB大小已經足夠了.但是對於較大型的資料庫來說,8MB遠遠不能滿足SQL Server頻繁活動的需要,因此會按照10%的比例增加,比如說需要1GB,則會需要較長的時間,此段時間會嚴重影響SQL Server的效能. 建議在SQL Server啟動時設定tempdb的初始化的大小(如片設定為MDF:300MB,LDF:50MB),也可以通過ALTER DATABASE來實現. 這樣在SQL Server在重啟時tempdb就會有足夠多的空間可利用,從而提高效率.

痛點在於找到合理的初始化大小,在SQL Server活動頻繁且tempdb不在增長時會是一個合適的值,可以設定此時的值為Initial Size;當然還會有更多的考量,此為一例.

3.3不要收縮tempdb(如沒有必要)

有時候我們會注意到tempdb佔用很大的空間,但是可用的空間會比較低時,會想到shrink資料庫來釋放磁碟空間, 此時要小心了,可能會影響到效能.

如所示:tempdb分配的空間為879.44MB,有45%的空間是閒置,如果shrink掉,可以釋放掉一部分磁碟空閑,但是之後SQL Server如有大量的操作時,tempdb空間不夠用,又會按照10%的比例自動成長. 這樣子的話,所做的shrink操作是無效的,還會增加系統的loading.

3.4 指派tempdb的檔案和其他資料檔案到不用的IO上

tempdb對IO的要求比較高,最好分配到高IO的磁碟上且與其他的資料檔案分到不用的磁碟上,以提高讀寫效率.

tempdb也分成多個檔案,一般會根據CPU來分,幾個CPU就分幾個tempdb的資料檔案. 多個tempdb檔案可以提高讀寫效率並且減少IO活動的衝突.