tempdb 資料庫

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

本文描述了 tempdb 系統資料庫，這是一個全域資源，可供所有連線至 SQL Server 執行個體、Azure SQL 資料庫或 Azure SQL 受控執行個體的使用者使用。

概觀

tempdb 系統資料庫是一種全域資源，其保存了：

  • 明確建立的暫存「使用者物件」。 這類物件包括全域或本機暫存資料表與索引、暫存預存程序、資料表變數、資料表值函數中傳回的資料表，以及資料指標。

  • 資料庫引擎建立的「內部物件」。 包括：

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

    每個內部物件至少會使用九個分頁：一個 IAM 分頁以及一個八頁範圍。 如需分頁與範圍的詳細資訊，請參閱分頁與範圍。

  • 「版本存放區」是保存資料列的資料頁集合，這些資料列支援資料列版本設定的功能。 有兩個類型：一般版本存放區與線上編製索引版本存放區。 版本存放區包含：

    • 由資料庫中資料修改交易所產生的資料列版本，該資料庫會透過資料列版本設定隔離或快照集隔離交易使用 READ COMMITTED。
    • 由資料修改交易針對線上索引作業、Multiple Active Result Set (MARS) 與 AFTER 觸發程序之類的功能所產生的資料列版本。

至少會記錄 tempdb 內的作業，以便復原交易。 每次啟動 Microsoft SQL Server 時都會重新建立 tempdb，如此一來，系統便可一律以乾淨的資料庫複本啟動。 連接中斷時會自動卸除暫存資料表與預存程序，且系統關閉時所有連接都會停止。

tempdb 絕對不會有任何要從 SQL Server 的一個工作階段儲存至其他工作階段的資料。 tempdb 不允許進行備份與還原作業。

SQL Server 中 tempdb 的實體屬性

下表列出 SQL Server 中 tempdb 資料與記錄檔的初始設定值。 這些值會以 model 資料庫的預設值為基礎。 對於不同版本的 SQL Server，這些檔案的大小可能會稍有不同。

檔案 邏輯名稱 實體名稱 初始大小 檔案成長
主要資料 tempdev tempdb.mdf 8 MB 自動成長 64 KB，直到磁碟滿了為止
次要資料檔案 temp# tempdb_mssql_#.ndf 8 MB 自動成長 64 KB，直到磁碟滿了為止
Log templog templog.ldf 8 MB 自動成長 64 MB，最大至 2 TB。

次要資料檔案的數目取決於電腦上 (邏輯) 處理器的數目。 一般而言，如果邏輯處理器的數目小於或等於 8，請使用與邏輯處理器數目相同的資料檔案數目。 如果邏輯處理器的數目大於八，則使用八個資料檔案。 接著，如果競爭持續發生，請以四的倍數增加資料檔案數目，直到競爭縮減到可接受的程度，或對工作負載/程式碼進行變更為止。

資料檔案數目預設值取決於 KB 2154845內的一般指導方針。

如果要檢查 tempdb 的目前大小和成長參數，請查詢 tempdb.sys.database_files 檢視。

移動 SQL Server 中的 tempdb 資料和記錄檔

若要移動 tempdb 資料與記錄檔，請參閱移動系統資料庫。

SQL Server 中於 tempdb 的資料庫選項

下表列出 tempdb 資料庫中每個資料庫選項的預設值，以及是否可修改該選項。 若要檢視這些選項目前的設定，請參閱 sys.databases 目錄檢視。

資料庫選項 預設值 可以修改
ALLOW_SNAPSHOT_ISOLATION OFF 是
ANSI_NULL_DEFAULT OFF 是
ANSI_NULLS OFF 是
ANSI_PADDING OFF 是
ANSI_WARNINGS OFF 是
ARITHABORT OFF 是
AUTO_CLOSE OFF 否
AUTO_CREATE_STATISTICS 開啟 是
AUTO_SHRINK OFF 否
AUTO_UPDATE_STATISTICS 開啟 是
AUTO_UPDATE_STATISTICS_ASYNC OFF 是
CHANGE_TRACKING OFF 否
CONCAT_NULL_YIELDS_NULL OFF 是
CURSOR_CLOSE_ON_COMMIT OFF 是
CURSOR_DEFAULT GLOBAL 是
資料庫可用性選項 ONLINE

MULTI_USER

READ_WRITE		 否

無

否
DATE_CORRELATION_OPTIMIZATION OFF 是
DB_CHAINING 開啟 否
ENCRYPTION OFF 否
MIXED_PAGE_ALLOCATION OFF 否
NUMERIC_ROUNDABORT OFF 是
PAGE_VERIFY 新安裝 Microsoft SQL Server 的 CHECKSUM

若是 SQL Server 升級則為 NONE		 是
PARAMETERIZATION 簡單 是
QUOTED_IDENTIFIER OFF 是
READ_COMMITTED_SNAPSHOT OFF 否
RECOVERY 簡單 否
RECURSIVE_TRIGGERS OFF 是
Service Broker 選項 ENABLE_BROKER 是
TRUSTWORTHY OFF 否

如需這些資料庫選項的描述，請參閱 ALTER DATABASE SET 選項 (Transact-SQL)。

Azure SQL 中的 tempdb

Azure SQL Database 中 tempdb 的行為與 SQL Server、Azure SQL 受控執行個體和 Azure VM 上 SQL Server 的行為不同。

Azure SQL Database 中的 tempdb

Azure SQL Database 中的單一和集區資料庫支援全域暫存資料表和全域暫存預存程序，範圍為資料庫層級，並儲存在 tempdb 中。 相同資料庫中所有使用者的工作階段，皆會共用全域暫存資料表與全域暫存預存程序。 其他資料庫使用者的工作階段無法存取全域暫存資料表。 如需詳細資訊，請參閱限定資料庫範圍的全域暫存資料表 (Azure SQL Database)。

對於單一資料庫，邏輯伺服器上的每個單一資料庫都有自己的 tempdb。 在同一個彈性集區中，tempdb 是相同集區中所有資料庫的共用資源，但其他資料庫中看不到在一個資料庫中建立的暫存物件。

除了所有系統資料庫外，Azure SQL Database 中的單一和集區資料庫只能存取 master 資料庫和 tempdb 資料庫。 如需詳細資訊，請參閱什麼是 Azure 中的邏輯伺服器？

如需深入瞭解 Azure SQL Database 中的 tempdb 大小，請檢閱：

SQL 受控執行個體中的 tempdb

Azure SQL 受控執行個體支援暫存物件的方式與 SQL Server 相同，所有全域暫存資料表和全域暫存預存程序皆可由相同受控執行個體內的所有使用者透過工作階段存取。 同樣地，所有系統資料庫皆可供存取。

您可以設定 tempdb 檔案數目、其增長增量，以及其大小上限。 如需在 Azure SQL 受控執行個體中進行 tempdb 設定的詳細資訊，請參閱進行 Azure SQL 受控執行個體的 tempdb 設定。

如需深入了解 Azure SQL 受控執行個體中的 tempdb 大小，請檢閱資源限制。

限制

下列作業無法在 tempdb 資料庫上執行：

  • 加入檔案群組。
  • 備份或還原資料庫。
  • 變更定序。 預設定序是伺服器定序。
  • 變更資料庫擁有者。 tempdb 是由 sa 所擁有。
  • 建立資料庫快照集。
  • 卸除資料庫。
  • 從資料庫卸除 guest 使用者。
  • 啟用異動資料擷取。
  • 參與資料庫鏡像。
  • 移除主要檔案群組、主要資料檔案或記錄檔。
  • 重新命名資料庫或主要檔案群組。
  • 執行 DBCC CHECKALLOC。
  • 執行 DBCC CHECKCATALOG。
  • 將資料庫設定為 OFFLINE。
  • 將資料庫或主要檔案群組設定為 READ_ONLY。

權限

任何使用者都可以在 tempdb 中建立暫存物件。 除非使用者接收到其他權限，否則只能存取自己的物件。 您可以撤銷對 tempdb 的連線權限，以防止使用者使用 tempdb。 我們不建議這樣做，因為有些例行作業需要使用 tempdb。

最佳化 SQL Server 中的 tempdb 效能

tempdb 資料庫的大小和實體位置會影響系統效能。 例如，如果為 tempdb 定義的大小太小，每次您重新啟動 SQL Server 的執行個體時，部分系統處理負載可能會開始讓 tempdb 自動成長到支援工作負載所需的大小。

如果可能，請使用檔案立即初始化來改善資料檔案成長作業的效能。

您可將檔案大小設定為夠大的值來容納環境中的典型工作負載，藉此為所有 tempdb 檔案預先配置空間。 預先配置可防止 tempdb 擴充過於頻繁而影響效能。 tempdb 資料庫應該會設為自動成長，以針對非計劃性的例外狀況增加磁碟空間。

由於 SQL Server 使用的比例填入演算法比較偏好可用空間較大的檔案配置，因此，每個檔案群組內的資料檔案大小應該會相同。 將 tempdb 分割成相同大小的多個資料檔案時，可讓使用 tempdb 的作業具有較高的平行效率。

將檔案成長增量設定為合理的大小，並在所有資料檔案中設定相同的增量，以防止 tempdb 資料庫檔案增加的數值太小。 相較於寫入到 tempdb 的資料量，如果檔案成長太小，那麼 tempdb 可能必須透過自動成長事件時常擴大。 自動成長事件會對效能造成負面影響。

若要檢查 tempdb 目前的大小與成長參數，請使用下列查詢：

 SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.max_size
     WHEN 0 THEN 'Autogrowth is off.'
     WHEN -1 THEN 'Autogrowth is on.'
     ELSE 'Log file grows to a maximum size of 2 TB.'
   END,
   growth_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

將 tempdb 資料庫放在快速的 I/O 子系統上。 如果有許多直接連接的磁碟，請使用磁碟條狀配置。 除非您也遇到了 I/O 瓶頸，否則 tempdb 資料檔案的個別檔案或群組不一定要位於不同磁碟或主軸上。

將 tempdb 資料庫放在與使用者資料庫所使用磁不同的磁碟碟上。

注意

即使針對 tempdb，資料庫選項 DELAYED_DURABILITY 設定為 DISABLED ，SQL Server 仍會使用延遲認可來排清磁碟的 tempdb 記錄變更，因為 tempdb 會在啟動時建立，而且無需執行復原處理序。

SQL Server 中的 tempdb 效能改善

在 SQL Server 2016 (13.x) 中推出

  • 系統會快取暫存資料表和資料表變數。 快取可讓卸除及建立暫存物件的作業以極快的速度執行。 快取也可以減少分頁配置與中繼資料競爭。
  • 已改善配置分頁閂鎖通訊協定，以減少所使用的 UP (更新) 閂鎖數目。
  • tempdb 的記錄負荷已縮減，以降低 tempdb 記錄檔的磁碟 I/O 頻寬耗用量。
  • 安裝程式會在新的執行個體安裝期間新增多個 tempdb 資料檔案。 您可以使用 [資料庫引擎組態] 區段中的新 UI 輸入控制項與命令列參數 /SQLTEMPDBFILECOUNT 來完成此工作。 根據預設，安裝程式會新增與邏輯處理器計數一樣多的 tempdb 資料檔案 (或是 8 個)，以較低者為準。
  • 如果有多個 tempdb 資料檔案，則視成長設定而定，所有檔案都會同時以相同數量自動成長。 不再需要追蹤旗標 1117。 如需詳細資訊，請參閱 TEMPDB 和使用者資料庫的 -T1117 和 -T1118 變更。
  • tempdb 中的所有配置都使用統一範圍。 不再需要追蹤旗標 1118。 如需 tempdb 中效能改善的詳細資訊，請參閱部落格文章 TEMPDB - 檔案和追蹤旗標與更新！\(英文\)。
  • 針對主要檔案群組，AUTOGROW_ALL_FILES 屬性已開啟且無法修改。

在 SQL Server 2017 (14.x) 中推出

  • SQL 設定體驗可改善初始 tempdb 檔案配置的指導方針。 如果初始檔案大小設定為大於 1 GB 的值，且未啟用檔案立即初始化，則 SQL 設定會警告客戶，從而避免執行個體啟動延遲。
  • SQL Server 2017 中推出了全新 DMV sys.dm_tran_version_store_space_usage，以追蹤每個資料庫的版本存放區用量。 此全新 DMV 有助於針對 DBA 的版本存放區用量監視 tempdb，DBA 可根據每個資料庫的版本存放區用量需求主動規劃 tempdb 大小。
  • 全新的智慧型查詢處理功能，例如自適性聯結和記憶體授與意見反應，可減少查詢連續執行的記憶體溢寫，減少不必要的 tempdb 使用率。

在 SQL Server 2019 (15.x) 中推出

  • 自 SQL Server 2019 (15.x) 起，SQL Server 在開啟 tempdb 檔案以允許磁碟輸送量上限時，不會使用 FILE_FLAG_WRITE_THROUGH 選項。 由於 tempdb 是在 SQL Server 啟動時重新建立，因此不需要這些選項，因為它們可讓其他系統資料庫和使用者資料庫實現資料一致性。 如需 FILE_FLAG_WRITE_THROUGH 的詳細資訊，請參閱記錄和資料儲存演算法，以擴充 SQL Server 中的資料可靠性。
  • 記憶體最佳化 TempDB 中繼資料會移除 tempdb 中 PAGELATCH 等候的瓶頸，並解除鎖定新的可擴縮性層級。 如需詳細資訊，請觀看此做法 (及時間) 影片示範：記憶體最佳化 TempDB 中繼資料。 如需詳細資訊，請參閱監視記憶體最佳化 tempdb 中繼資料並進行疑難排解。
  • 並行分頁可用空間 (PFS) 頁面更新可減少所有資料庫中的修補程式閂鎖競爭，這是 tempdb 中最常見的問題。 這種改進會改變使用 PFS 更新來管理並行的方式，使其可以在共用閂鎖下更新，而不是獨佔閂鎖。 此行為在從 SQL Server 2019 (15.x) 開始的所有資料庫 (包括 TempDB) 中預設為啟用。 如需 PFS 頁面的詳細資訊，請參閱涵蓋範圍：GAM、SGAM 和 PFS 頁面。
  • 根據預設，在 Linux 上進行新的 SQL Server 安裝，會依據邏輯核心的數目建立多個 tempdb 資料檔案 (最多八個資料檔案)。 此情況不適用於就地的次要或主要版本升級。 每個 tempdb 檔案為 8 MB，且可自動增長到 64 MB。 此行為類似於 Windows 上的預設 SQL Server 安裝。

在 SQL Server 2022 (16.x) 中推出

經記憶體最佳化的 tempdb 中繼資料

對於在 SQL Server 上執行眾多工作負載來說，tempdb 中的中繼資料競爭一直以來都是可擴縮性的瓶頸。 SQL Server 2019 (15.x) 推出一項新功能，這是記憶體內部資料庫系列功能之一：記憶體最佳化 TempDB 中繼資料。

此功能可有效地移除此瓶頸，並針對 tempdb 繁重的工作負載，解除鎖定新層級的可擴縮性。 在 SQL Server 2019 (15.x) 中，可將涉及管理暫存資料表中繼資料的系統資料表移至免用閂鎖、非持久性且記憶體最佳化的資料表。

注意

目前 Azure SQL 資料庫或 Azure SQL 受控執行個體無法使用記憶體最佳化 TempDB 中繼資料功能。

請觀看這段七分鐘的影片，以概略了解如何及何時使用記憶體最佳化 TempDB 中繼資料：

設定並使用記憶體最佳化 TempDB 中繼資料

若要選擇加入這個新功能，請使用下列指令碼：

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

這項設定變更需要重新啟動服務才會生效。

您可以使用以下 T-SQL 命令驗證 tempdb 是否經記憶體最佳化：

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

若伺服器在您啟用經記憶體最佳化 TempDB 中繼資料後因任何原因而無法啟動，則您可以透過 -f 啟動選項，以最低組態啟動 SQL Server 執行個體來略過此功能。 您接著可以停用此功能，然後以一般模式重新啟動 SQL Server。

若要防止伺服器發生記憶體不足的狀況，您可以將 tempdb 繫結至資源集區。 此作業須透過 ALTER SERVER 命令來完成，而非執行您將資源集區繫結至資料庫時通常會遵循的步驟。

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');

此變更也需要重新啟動才會生效，即使已啟用記憶體最佳化 TempDB 中繼資料，仍是如此。

記憶體最佳化 tempdb 限制

  • 功能開關切換不是動態的。 因為內部變更需要作用於 tempdb 的結構，所以啟用或停用此功能都必須重新開機。

  • 不允許單一交易存取多個資料庫中經記憶體最佳化的資料表。 任何涉及使用者資料庫中經記憶體最佳化資料表的交易，都不能在相同交易中存取 tempdb 系統檢視。 如果您嘗試在與使用者資料庫中經記憶體最佳化資料表相同的交易中存取 tempdb 系統檢視，將會收到下列錯誤：

    A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.

    範例：

    BEGIN TRAN;

SELECT *
FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb

INSERT INTO <user database>.<schema>.<mem-optimized table>
VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail

COMMIT TRAN;

  • 針對經記憶體最佳化資料表的查詢不支援鎖定和隔離提示，因此，針對經記憶體最佳化 tempdb 目錄檢視的查詢將不支援鎖定和隔離提示。 至於 SQL Server 中的其他系統目錄檢視，針對系統檢視的所有交易都會隔離在 READ COMMITTED (或在此範例中為 READ COMMITTED SNAPSHOT)。

  • 若啟用了經記憶體最佳化 TempDB 中繼資料，就無法在暫存資料表上建立資料行存放區索引。

  • 由於資料行存放區索引的限制，若啟用了經記憶體最佳化 TempDB 中繼資料，則不支援使用 sp_estimate_data_compression_savings 系統預存程序搭配 COLUMNSTORE 或 COLUMNSTORE_ARCHIVE 資料壓縮參數。

  • 透過系統預存程序，便可用手動方式使記憶體內部引擎釋出符合記憶體回收條件，與已刪除的記憶體內資料之資料列相關的記憶體。 這有助於針對記憶體最佳化 tempdb 中繼資料 (HkTempDB) 記憶體不足的錯誤進行疑難排解。 如需詳細資訊，請參閱 sys.sp_xtp_force_gc (Transact-SQL)。

注意

只有當您參考 tempdb 系統檢視時，才適用這些限制。 如有需要，您可以在與存取使用者資料庫中經記憶體最佳化資料表相同的交易中建立暫存資料表。

SQL Server 中 tempdb 的容量規劃

在決定 SQL Server 實際執行環境中的 tempdb 適當大小時，您需要考量許多因素。 如先前所述，這些因素包括現有的工作負載與所使用的 SQL Server 功能。

我們建議您在 SQL Server 測試環境中執行下列工作來分析現有的工作負載：

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

監視 tempdb 使用

tempdb 中的磁碟空間不足，可能會在 SQL Server 實際執行環境中造成嚴重的中斷。 其也會阻止執行中的應用程式完成作業。 您可以使用 sys.dm_db_file_space_usage 動態管理檢視來監視 tempdb 檔案中所使用的磁碟空間。

例如，下列四個範例指令碼會尋找 tempdb 中的可用空間量、版本存放區使用的空間量、內部物件使用的空間量，以及使用者物件使用的空間量：

 -- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

若要在工作階段或工作層級監視 tempdb 中的分頁配置或解除配置活動，您可以使用 sys.dm_db_session_space_usage 與 sys.dm_db_task_space_usage 動態管理檢視。 這些檢視可協助您識別佔用大量 tempdb 磁碟空間的大型查詢、暫存資料表或資料表變數。 您也可以使用數個計數器來監視 tempdb 中的可用空間，以及正在使用 tempdb 的資源。

例如，使用下列指令碼來取得每個工作階段中所有目前執行中工作的內部物件所取用的 tempdb 空間：

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

使用下列指令碼來尋找目前工作階段中執行中和已完成工作的內部物件所取用的 tempdb 空間：

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;

相關內容