最佳化tempdb提高SQL Server的效能

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

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

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,就該返回來考慮這些問題了。

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、不要收縮tempdb(如沒有必要)

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

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

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

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

tempdb是SQL Server重要的一部分,以上只是對tempdb的一些瞭解總結,還需要進一步學習。

 

http://news.ccidnet.com/art/32849/20100517/2061689_1.html