發行項
04/02/2012

tempdb 的容量計畫

本主題提供決定 tempdb 所需之適當磁碟空間量的指導方針。這個主題也包括有關如何設定 tempdb 以便在實際執行環境中達到最佳效能的建議，以及有關如何監督 tempdb 空間使用量的資訊。

如何使用 tempdb

tempdb 系統資料庫是全域資源，可供所有連接到 SQL Server 執行個體的使用者使用。tempdb 資料庫是用來儲存下列物件：使用者物件、內部物件和版本存放區。

使用者物件

使用者物件由使用者明確建立。這些物件可能在使用者工作階段的範圍內或在建立該物件之常式的範圍內。常式是指預存程序、觸發程序或使用者自訂函數。使用者物件可為下列其中之一：

使用者自訂資料表和索引
系統資料表和索引
全域暫存資料表和索引
本機暫存資料表和索引
資料表變數
資料表值函式中傳回的資料表

內部物件

內部物件是由 SQL Server Database Engine 視需要建立，來處理 SQL Server 陳述式。內部物件是在陳述式的範圍內建立及卸除。內部物件可為下列其中之一：

用於資料指標或多工緩衝處理作業和暫存大型物件 (LOB) 儲存體的工作資料表。
用於雜湊聯結或雜湊彙總作業的工作檔案。
如建立或重建索引之類的作業 (若有指定 SORT_IN_TEMPDB) 或特定 GROUP BY、ORDER BY 或 UNION 查詢的中繼排序結果。

每一個內部物件使用最少 9 頁；一個 IAM 頁面和一個 8 頁範圍。如需有關分頁與範圍的詳細資訊，請參閱＜了解頁面與範圍＞。

版本存放區

版本存放區是保存資料列之資料頁的集合，必須要有這些資料列才能支援使用資料列版本控制的功能。版本存放區有兩個：一個是一般版本存放區，一個是線上索引建立版本存放區。版本存放區包含下列各項：

由資料庫中的資料修改交易所產生的資料列版本，該資料庫採用使用資料列版本控制隔離層級的快照集或讀取認可。
由以下這類功能的資料修改交易所產生的資料列版本：線上索引作業、Multiple Active Result Set (MARS) 和 AFTER 觸發程序。

下表列出 SQL Server 中的功能，它們在 tempdb 中建立使用者物件、內部物件或資料列版本。可能的話，會提供預估磁碟空間使用量的方法。

功能	tempdb 使用量	其他資訊
已啟用觸發程序的大量載入作業	啟用觸發程序時可使用大量匯入最佳化。SQL Server 對於更新或刪除交易的觸發程序，使用資料列版本控制。每一個刪除或更新之資料列的副本會加入版本存放區中。請參閱本表後面的「觸發程序」。	最佳化大量匯入效能
一般資料表運算式查詢	一般資料表運算式可視為在單一 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 陳述式之執行範圍內定義的暫存結果集。當一般資料表運算式查詢的查詢計畫使用多工緩衝處理運算子來儲存中繼查詢結果時，Database Engine 就會在 tempdb 中建立工作資料表，以便支援這項作業。	使用一般資料表運算式 WITH common_table_expression (Transact-SQL)
資料指標	索引鍵集驅動及靜態資料指標會使用 tempdb 內建的工作資料表。索引鍵集驅動資料指標會使用工作資料表，來儲存識別資料指標中資料列的索引鍵集。靜態資料指標使用工作資料表來儲存資料指標的完整結果集。資料指標的磁碟空間使用量可能不同，視所選擇的查詢計畫而定。如果查詢計畫與 SQL Server 的舊版相同，則磁碟空間使用量大致相同。	關於選擇資料指標類型
Database Mail	請參閱本表後面的「Service Broker」。	Database Mail
DBCC CHECKDB	DBCC CHECKDB 使用 tempdb 工作資料表來保存中繼結果和用於排序作業。若要判斷作業的 tempdb 磁碟空間需求，請執行 DBCC CHECKDB WITH ESTIMATEONLY。	DBCC CHECKDB (Transact-SQL) 最佳化 DBCC CHECKDB 效能
事件通知	請參閱本表後面的「Service Broker」。	了解事件通知
索引	當您建立或重建索引 (離線或連線)，並將 SORT_IN_TEMPDB 選項設為 ON 時，即指示 Database Engine 使用 tempdb 來儲存用來建立索引的中繼排序結果。指定 SORT_IN_TEMPDB 後需要排序時，tempdb 必須有足夠的磁碟空間來保存最大索引，以及有相當於 index create memory option 值的磁碟空間。如需詳細資訊，請參閱＜索引磁碟空間範例＞。資料表和索引可以分割。對於分割的索引，如果指定了 SORT_IN_TEMPDB 索引選項，且索引與基底資料表對齊，則 tempdb 必須有足夠空間來保存最大資料分割的中繼排序作業。如果索引沒有對齊，則 tempdb 必須有足夠空間來保存所有資料分割的中繼排序作業。如需詳細資訊，請參閱＜資料分割索引的特殊指導方針＞。線上索引作業會使用資料列版本控制，來隔離索引作業和其他交易所進行的修改效果。資料列版本控制可避免要求已讀取之資料列的共用鎖定。線上索引作業期間進行的並行使用者更新和刪除作業，將需要一些空間在 tempdb 中產生版本記錄。當線上索引作業使用 SORT_IN_TEMPDB 而且需要排序時，tempdb 也必須有前述的其他磁碟空間來存放中繼排序結果。建立、卸除或重新建立叢集索引的線上索引作業也需要其他磁碟空間來建立及維護暫存對應索引。CREATE 和 UPDATE STATISTICS 作業可以使用 tempdb 來排序資料列的範例，以便建立統計資料。如需詳細資訊，請參閱＜索引 DDL 作業的磁碟空間需求＞。	建立 tempdb 與索引資料分割索引的特殊指導方針索引 DDL 作業的磁碟空間需求索引磁碟空間範例線上索引作業如何運作
大型物件 (LOB) 資料類型變數和參數	大型物件資料類型為 varchar(max)、nvarchar(max)、varbinary(max)text、ntext、image 和 xml。這些類型的大小最多為 2 GB，可作為預存程序、使用者自訂函數、批次或查詢中的變數或參數。如果值很小，則定義為 LOB 資料類型的參數和變數可使用主記憶體作為儲存體。不過，較大值則儲存在 tempdb 中。當 LOB 變數和參數儲存在 tempdb 時，它們被視為內部物件。您可以查詢 sys.dm_db_session_space_usage 動態管理檢視，來報告對於給定的工作階段所配置之內部物件的頁數。有些內建字串函數 (例如 SUBSTRING 或 REPLICATE) 在操作 LOB 值時，可能需要 tempdb 的中繼暫時儲存體。同樣地，當在資料庫上啟用以資料列版本控制為基礎的交易隔離等級，並對大型物件進行修改時，會將已變更的 LOB 片段複製到 tempdb 的版本存放區中。	使用大數值資料類型
Multiple Active Result Set (MARS)	在單一連接之下可發生多個作用中的結果集；一般稱為 MARS。如果 MARS 工作階段在有作用中結果集的情況下，發出資料修改陳述式 (例如 INSERT、UPDATE 或 DELETE)，受到修改陳述式影響的資料列會儲存在 tempdb 的版本存放區中。請參閱本表後面的「資料列版本控制」。	使用 Multiple Active Result Sets (MARS)
查詢通知	請參閱本表後面的「Service Broker」。	使用查詢通知
查詢	包含 SELECT、INSERT、UPDATE 和 DELETE 陳述式的查詢可使用內部物件來儲存雜湊聯結、雜湊彙總或排序的中繼結果。快取查詢執行計畫時，會快取計畫所需要的工作資料表。快取工作資料表時，資料表會被截斷，有 9 頁會留在快取中供重複使用。這樣可改進下次執行查詢的效能。如果系統的記憶體不足，Database Engine 可移除此執行計畫並卸除相關聯的工作資料表。	執行計畫快取與重複使用
資料列版本控制	資料列版本控制是一般架構，可用來支援下列功能：觸發程序 Multiple Active Result Set (MARS) 指定 ONLINE 選項的索引作業以資料列版本控制為基礎的交易隔離等級：新的讀取認可隔離等級實作方式，其使用資料列版本控制來提供陳述式層級的讀取一致性。快照隔離等級，可以提供交易等級的讀取一致性。只要有作用中交易必須存取它，資料列版本就會保留在 tempdb 版本存放區中。目前版本存放區的內容是傳回 sys.dm_tran_version_store 中。版本存放區頁面是在檔案層級追蹤，因為它們是全域資源。您可以使用 sys.dm_db_file_space_usage 中的 version_store_reserved_page_count 資料行來檢視版本存放區的目前大小。版本存放區清除必須考慮需要存取特定版本之執行最久的交易。可在 sys.dm_tran_active_snapshot_database_transactions 中檢視 elapsed_time_seconds 資料行，來探索與版本存放區清除相關之執行最久的交易。Transactions 物件中的 Free Space in Tempdb (KB) 和 Version Store Size (KB) 計數器，可用來監視 tempdb 中的資料列版本存放區的大小和成長率。如需詳細資訊，請參閱＜SQL Server 的 Transactions 物件＞。若要預估 tempdb 需要多少空間供資料列版本控制，您必須先考慮，作用中交易必須在版本存放區保留其所有變更。這表示後來啟動的快照集交易可以存取舊版本。而且，如果有作用中快照集交易，則當快照集啟動時，作用中的交易產生的所有版本存放區資料也必須保留。以下是基本公式： [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction]	瞭解以資料列版本控制為基礎的隔離等級資料列版本控制資源的使用方式
Service Broker	Service Broker 可以協助開發人員建立非同步、鬆散偶合的應用程式，使獨立的元件可以在其中一起運作來完成工作。這些應用程式元件交換包含要完成工作所需之資訊的訊息。Service Broker 明確使用 tempdb 來保留無法留在記憶體的現有對話內容。每個對話的大小大約是 1KB。此外，Service Broker 隱含使用 tempdb，它快取查詢執行內容中的物件，例如用於計時器事件和背景傳遞交談的工作資料表。 Database Mail、Event Notifications 和 Query Notifications 隱含使用 Service Broker。	概觀 (Service Broker)
預存程序	預存程序可建立使用者物件，例如全域或本機暫存資料表及其索引、變數或參數。預存程序中的暫存物件可以快取，使卸除和建立這些物件的作業最佳化。此行為可增加 tempdb 磁碟空間需求。每個暫存物件最多可儲存 9 頁供重複使用。請參閱本表後面的「暫存資料表和 table 變數」。	建立預存程序 (Database Engine)
暫存資料表和 table 變數使用者自訂資料表和索引系統資料表和索引全域暫存資料表和索引本機暫存資料表和索引 table 變數資料表值函式中傳回的資料表	暫存資料表和 table 變數儲存在 tempdb 中。暫存資料表物件的磁碟空間需求與 SQL Server 的舊版相同。預估暫存資料表大小的方法與預估標準資料表大小的方法相同。如需詳細資訊，請參閱＜估計資料表的大小＞。 table 變數的行為類似於本機變數。table 變數是 table 類型，主要用於資料列集的暫存儲存體，此資料列集是傳回作為資料表值函式的結果集。保存 table 變數所需的磁碟空間，視所宣告變數的大小和儲存在變數中的值而定。當下列條件符合時，就會快取本機暫存資料表和變數：未建立具名條件約束。在建立暫存資料表之後，未執行影響資料表的資料定義語言 (DDL) 陳述式，例如 CREATE INDEX 或 CREATE STATISTICS 陳述式。不使用動態 SQL 建立暫存物件，例如：sp_executesql N'create table #t(a int)'。暫存物件是建立在另一個物件之內，例如預存程序、觸發程序、使用者自訂函數；或是使用者自訂之資料表值函式的傳回資料表。快取暫存資料表或 table 變數後，若其用途結束，不會刪除暫存物件。而是截斷暫存物件。下次執行此呼叫物件時，可儲存及重複使用最多 9 頁。快取可使卸除和建立物件的作業快速執行，減少頁面配置的爭用。若要達到最佳效能，您應該使用下列公式，計算快取本機暫存資料表或 tempdb 中的 table 變數所需的磁碟空間： 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure	CREATE TABLE (Transact-SQL) 使用變數和參數 (Database Engine) DECLARE @local_variable (Transact-SQL)
觸發程序	於 AFTER 觸發程序中使用的 inserted 和 deleted 資料表是在 tempdb 中建立的。也就是說，觸發程序所更新或刪除的資料列有版本化。這包括引發該觸發程序的陳述式所修改的全部資料列。觸發程序插入的資料列不會版本化。 INSTEAD OF 觸發程序以類似查詢的方式使用 tempdb。INSTEAD OF 觸發程序的磁碟空間使用量與 SQL Server 的舊版相同。請參閱本表前面的「查詢」。如果您在啟用觸發程序的情況下大量載入資料，則會將每個已刪除或更新之資料列的副本加入版本存放區中。	CREATE TRIGGER (Transact-SQL) 最佳化大量匯入效能資料列版本控制資源的使用方式
使用者自訂函數	使用者自訂函數可建立暫存使用者物件，例如全域或本機資料表及其索引、變數或參數。例如，資料表值函式的傳回資料表是儲存在 tempdb 中。允許在純量函數和資料表值函式中使用之參數和傳回值的資料類型包括大部分 LOB 資料類型。例如，傳回值可以是 xml 或 varchar(max) 類型。請參閱本表前面的「大型物件 (LOB) 資料類型變數和參數」。資料表值使用者自訂函數中的暫存物件可以快取，使卸除和建立這些物件的作業最佳化。請參閱本表前面的「暫存資料表和 table 變數」。	CREATE FUNCTION (Transact-SQL)
XML	xml 類型的變數和參數最多為 2 GB。只要值不大，它們會使用主記憶體作為儲存體。不過，較大值則儲存在 tempdb 中。請參閱本表前面的「大型物件 (LOB) 資料類型變數和參數」。 sp_xml_preparedocument 系統預存程序在 tempdb 中建立工作資料表。MSXML 剖析器使用此工作資料表來儲存已剖析的 XML 文件。執行預存程序時，tempdb 的磁碟空間需求與指定的 XML 文件大小幾乎等比例。	在 SQL Server 中實作 XML sp_xml_preparedocument (Transact-SQL) 利用 OPENXML 查詢 XML

升級到 SQL Server 的容量規劃

在實際執行環境中決定 tempdb 的適當大小時，取決於許多因素。如本主題先前所述，這些因素包括現有的工作負載和使用的 SQL Server 功能。我們建議您在 SQL Server 測試環境中執行下列工作來分析現有的工作負載：

將 tempdb 的自動成長設為 on。
執行個別查詢或工作負載追蹤檔案和監視 tempdb 空間使用量。
執行索引維護作業，例如重建索引和監視 tempdb 空間。
使用先前步驟中的空間使用值來預測總工作負載使用量；針對預定的並行活動調整此值，然後據此設定 tempdb 的大小。

如需有關監視 tempdb 空間的詳細資訊，請參閱＜tempdb 磁碟空間不足的疑難排解＞。如需有關預估索引作業期間 tempdb 使用量的詳細資訊，請參閱＜索引磁碟空間範例＞。

設定實際執行環境的 tempdb

若要達到最佳 tempdb 效能，請遵循＜最佳化 tempdb 效能＞所提供的指引和建議。

如何監視 tempdb 使用量

tempdb 的磁碟空間用完會造成 SQL Server 實際執行環境嚴重中斷，並使執行中的應用程式無法完成作業。您可以使用 sys.dm_db_file_space_usage 動態管理檢視來監視 tempdb 檔中這些功能所使用的磁碟空間。另外，若要在工作階段或工作層級監視 tempdb 中的頁面配置或取消配置活動，您可以使用 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage 動態管理檢視。這些檢視可用來識別使用很多 tempdb 磁碟空間的大型查詢、暫存資料表或資料表變數。還有幾個計數器可用來監視 tempdb 可用的空間以及使用 tempdb 的資源。如需詳細資訊，請參閱＜tempdb 磁碟空間不足的疑難排解＞。

請參閱

共用方式為