SQL Server 2012筆記分享-18：了解并設定tempdb資料庫

TEMPDB簡介

tempdb 系統資料庫是一個全局資源（一個執行個體一個tempdb），可供連接配接到 SQL Server 執行個體的所有使用者使用，并可用于儲存下列各項：

? 顯式建立的臨時使用者對象，例如全局或局部臨時表、臨時存儲過程、表變量或遊标。

? SQL Server 資料庫引擎建立的内部對象，例如，用于存儲假脫機或排序的中間結果的工作表。

? 由使用已送出讀（使用行版本控制隔離或快照隔離事務）的資料庫中資料修改事務生成的行版本。

? 由資料修改事務為實作聯機索引操作、多個活動的結果集 (MARS) 以及 AFTER 觸發器等功能而生成的行版本。

tempdb 中的操作是最小日志記錄操作。 這将使事務産生復原。 每次啟動 SQL Server 時都會重新建立 tempdb，進而在系統啟動時總是保持一個幹淨的資料庫副本。 在斷開聯接時會自動删除臨時表和存儲過程，并且在系統關閉後沒有活動連接配接。 是以 tempdb 中不會有什麼内容從一個 SQL Server 會話儲存到另一個會話。 不允許對 tempdb 進行備份和還原操作。

Tempdb預設情況下使用的是簡單模式，在簡單模式下記錄最小日志。

TEMPDB的預設配置

對于不同版本的 SQL Server，這些檔案的大小可能略有不同。

tempdb 的大小可以影響系統性能。 例如，如果 tempdb 的大小太小，則每次啟動 SQL Server 時，系統處理可能忙于資料庫的自動增長，而不能支援工作負荷要求。 可以通過增加 tempdb 的大小來避免此開銷。

在 SQL Server 中，tempdb 性能以下列方式進行提高：

? 可能緩存臨時表和表變量。 緩存允許删除和建立臨時對象的操作非常快速地執行，并減少頁配置設定的争用問題。

? 配置設定頁闩鎖協定得到改善。 進而減少使用的 UP（更新）闩鎖數。

? 減少了 tempdb 的日志開銷。 進而降低了 tempdb 日志檔案上的磁盤 I/O 帶寬占用。

? 在 tempdb 中配置設定混合頁的算法得到改善。

任何使用者都可以在 tempdb 中建立臨時對象。 使用者隻能通路自己的對象，除非他們獲得更多的權限。 可以撤消對 tempdb 的連接配接權限以阻止使用者使用 tempdb，但是不建議這樣做，因為一些例行操作需要使用 tempdb。

TEMPDB的預設權限如圖。

參考：​​http://msdn.microsoft.com/zh-cn/library/ms190768.aspx​​

重新開機伺服器或者重新開機SQL server服務可以起到清理TEMPDB的作用，如圖。

目前tempDB的臨時表。

下圖是重新開機SQL server服務之後的臨時表情況。

如果tempdb占用了太多的空間，我們還可以針對tempdb進行收縮操作。

收縮的時候可以針對整個資料庫進行收縮，也可以針對某個tempdb的檔案進行收縮，如圖。

下面是一個收縮檔案的例子。

具體可以參考：如何收縮 SQL Server 中的 Tempdb 資料庫

http://support.microsoft.com/kb/307487/zh-cn

其他可供參考的有價值的文檔。

解決 tempdb 中磁盤空間不足的問題

http://technet.microsoft.com/zh-cn/library/ms176029(v=sql.105).aspx

tempdb 容量規劃

http://technet.microsoft.com/zh-cn/library/ms345368(v=SQL.105).aspx

Optimizing tempdb Performance

http://technet.microsoft.com/en-us/library/ms175527(v=SQL.105).aspx

======================================================================

關鍵概念

如果系統運作過程中 tempdb因需要自動增長了，SQL Serve不會記住增長後的大小，重新開機服務後仍然恢複到初始大小，但如果使用者使用了手工調整tempdb的大小，重新開機服務SQL Server會把tempdb重建為使用者指定大小。

當系統自動調整tempdb大小時，對檔案的讀寫将暫時的阻塞 。

是以如果我們預知tempdb将會增加到某個大小時，可以自行調整，進而避免性能下降。

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

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

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