SQL Server tempdb 初始大小對數(shù)據(jù)庫運行性能的影響

zhuyiwen

SQL Server 版本：2008 R2.

tempdb 在 SQL Server 實例安裝時，其初始大小為 8MB，增長設置為 10%。那么也就是說，當其大小超過 8MB 時會自動增長為 8.8MB 以滿足 tempdb 的需求。

那么，有沒有必要更改 tempdb 的初始大小？tempdb 的大小對整個 SQL Server 實例運行有無很大的影響呢？


答案是有。

最近，監(jiān)視某醫(yī)院的 SQL Server 數(shù)據(jù)庫的運行，發(fā)現(xiàn) tempdb.mdf 文件的實時大小達到了 513MB。通過跟蹤發(fā)現(xiàn)一個奇怪的現(xiàn)象，SELECT GETDATE()、SET NO_BROWSETABLE ON 語句的執(zhí)行時間(Duration)偶爾達到 2000 以上，正常的話應該是 0。遍查度娘，未找到一個解釋，非常郁悶。

于是，更改 tempdb 的初始大小為 1000MB，其后一周之內(nèi)的跟蹤，再沒有發(fā)現(xiàn)上述奇怪的現(xiàn)象。

現(xiàn)象解析：

......

SQL語句：

1. USE master
   
2. GO
   
3. 
   
4. ALTER DATABASE tempdb
   
5. MODIFY FILE
   
6.    (NAME = tempdev, SIZE = 1000MB)
   
7. GO

復制代碼

tmtony

期待更新！

zhuyiwen

tempdb系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫非常相似。主要的區(qū)別是，SQL服務器關閉后，tempdb中的數(shù)據(jù)不會持久保持。

每一次SQL Server重新啟動時，tempdb會從model數(shù)據(jù)庫復制。它從model數(shù)據(jù)庫中繼承了某些數(shù)據(jù)庫配置選項，如ALLOW_SNAPSHOT_ISOLATION。

tempdb中只有一個數(shù)據(jù)文件組和一個日志文件組。您可以配置文件的大小。當啟用自動增長（這是默認的），文件將增長到磁盤卷已滿為止。當服務器重啟時，tempdb文件大小會重置為配置的大�。�默認為8 MB）。自動增長是tempdb數(shù)據(jù)庫臨時（不同于其他類型的數(shù)據(jù)庫）空間。SQL服務器重新啟動時，重置tempdb數(shù)據(jù)庫。

用戶可以明確的在tempdb數(shù)據(jù)庫中創(chuàng)建和使用數(shù)據(jù)表。事務可以用于修改表中的數(shù)據(jù)，事務可以回滾。然而，沒有必要重做它們，因為tempdb的內(nèi)容不會在重新啟動SQL Server中保持。由于事務日志不需要刷新，事務提交的速度比用戶數(shù)據(jù)庫更快。在用戶數(shù)據(jù)庫，事務具有ACID屬性，即原子性、并發(fā)性、隔離和持久性。而在tempdb數(shù)據(jù)庫中，事務將失去持久性屬性。

SQL Server在tempdb數(shù)據(jù)庫中存儲內(nèi)部對象，如一個查詢的中間結果。tempdb數(shù)據(jù)庫中大部分內(nèi)部運作不生成日志記錄，因為不需要回滾。因此，這些操作速度更快。

tempdb數(shù)據(jù)庫不允許自動收縮。數(shù)據(jù)庫的文件收縮和收縮的能力是有限的。這是因為許多SQL Server存儲在tempdb中隱藏的對象，不能通過收縮操作移動。

zhuyiwen

tempdb 數(shù)據(jù)庫的空間使用

以下類型的對象可以占用tempdb數(shù)據(jù)庫的空間:

• 內(nèi)部對象
• 版本存儲
• 用戶對象
  

內(nèi)部對象:

• 存儲排序的中間結果。
• 存儲哈希聯(lián)接和哈希聚合的中間結果。
• 存儲XML變量或其它大對象（LOB）數(shù)據(jù)類型的變量。LOB數(shù)據(jù)類型包括所有的大對象類型：text、image、ntext、varchar(max)、varbinary(max)，以及其它。
• 用于需要池的查詢存儲中間結果。
• 用于鍵集游標存儲的鍵。
• 用于靜態(tài)游標存儲的查詢結果。
• 用于Broker服務存儲傳送的消息。
• 用于INSTEAD OF觸發(fā)器存儲中間處理數(shù)據(jù)。