tempdb對SQL Server效能的影響

1.SQL Server系統資料庫介紹

SQL Server有四個重要的系統級資料庫:master,model,msdb,tempdb.

master:記錄SQL Server系統的所有系統級資訊,包括執行個體範圍的中繼資料,端點,連結的伺服器和系統配置設定,還記錄其他資料庫是否存在以及這些資料問檔案的位置等等.如果master不可用,資料庫將不能啟動.

model:用在SQL Server 執行個體上建立的所有資料庫的模板。因為每次啟動 SQL Server 時都會建立 tempdb，所以 model 資料庫必須始終存在於 SQL Server 系統中。

msdb:由SQL Server Agent用來計劃警報和作業。

tempdb:是串連到 SQL Server 執行個體的所有使用者都可用的全域資源，它儲存所有暫存資料表,臨時工作表,暫存預存程序,臨時儲存大的類型,中間結果集,表變數和遊標等。另外，它還用來滿足所有其他臨時儲存要求.

 

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活動的衝突.