如何在SQL Server中優化Tempdb

by adonisy 4. 六月 2019 22:53

者:楊先民

審 稿:張智凱 
文章編號:
 
出刊日期:2019/06/04

SQL Server預設有四個系統資料庫,其中一個是tempdb。tempdb用於許多SQL操作,包含了使用者自訂的臨時物件,內部暫時性的物件和版本控制,以及其他功能,例如線上 online的 re-index工作,多資料結果集(MARS)等等。

 對 tempdb資料進進行最佳化是提高伺服器性能的最佳方式之一。

 本期就來介紹在 SQL Server中最佳化 tempdb的一些要訣。

值得注意的是,每個連結都使用相同tempdb資料庫,並沒有那種 A使用者使用的 tempdb資料庫和 B使用不同。

 在SQL Server中,一台實體不能有多個 tempdb資料庫,但是可以透過動態管理檢視(DMV)在tempdb中查詢到一些執行的操作的詳細資訊。

tempdb的功能和其屬性:

  •  
    • Tempdb一般會設定為 simple 復原模式,這代表的是已提交的交易日誌在 checkpoint 後會被標記成可重用。
    • Tempdb只能有一個檔案群組,不能增加更多檔案群組。
    • Tempdb可以存放三種類型的物件:使用者物件、,內部物件和版本控制資訊。

如何在SQL Server中最佳化Tempdb


由於是做資料的暫存,所以tempdb會被拿來做最佳化是必然的,但是該怎麼做?這裡有一些規則:

1.每次重新啟動後都會重建tempdb,並且為tempdb提供足夠大的初始大小。

2.由於經常讀取tempdb資料庫資料,所以把tempdb資料庫放在比較快的磁碟系統是必需的。

使用下面的 SQL指令來移動tempdb資料庫。

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME =, E: \DATA\tempdb.mdf ');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME =, E:\ Data\templog.ldf ');

GO

E槽可能是固態硬碟,總而言之就是速度愈快的磁碟愈好,當然固態硬碟是一個好選擇。

3.  替tempdb資料庫提供一個足夠的容量。並且設定讓它自動成長。 

4.設定復原模式為Simple。並非所有內容都記錄在 log之中,代表若是交易完成後, log資料將會自動刪除,這樣 log就不會成長。

5.可以根據 CPU的核心數量來設置 tempdb資料庫檔案個數,例如 CPU有 8顆則設定 tempdb有 8個資料檔案,在微軟原廠官方教材說,以 8這個

數字當成一個基準,若 CPU個數小於8,則設定檔案個數為小於 8的那個數字,若是 CPU大於 8的話,最多不會設定超過 8個檔案。

6.在 SQL Server 2016的版本中,安裝程式會預設分析你的 CPU個數來設定 tempdb資料庫的檔案個數。

7.把自動更新這個功能關閉,用以提高SQL Server資料庫中暫存資料的性能。

結論

tempdb資料庫需要多加注意,尤其是在SQL Server 2005之後的版本,因為版本控制是在 SQL Server 2005之後開始才大量使用 tempdb。


Tags:

SQL Server資料庫 | 楊先民Adonis Young

不允許評論

NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List