tempdb 資料庫

  • 發行項

適用範圍： 是SQL Server (所有支援的版本) 是Azure SQL Database

本文描述 tempdb 系統資料庫、可供所有連接到實例 SQL Server 、Azure SQL Database 或 Azure SQL 受控執行個體的使用者使用的全域資源。

概觀

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

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

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

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

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

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

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

至少會記錄 tempdb 內的作業，以便復原交易。 每次啟動 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.sys.database_files 的 tempdb 目前大小和成長參數。

移動 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 CHECKSUM (適用於 SQL Server 的新安裝)

NONE (適用於 SQL Server 的升級)		 是
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 的行為與 azure vm 上的行為 SQL Server、azure SQL 受控執行個體和 SQL Server 不同。

SQL Database 中的 tempdb

Azure SQL Database中的單一和集區資料庫支援全域臨時表，以及範圍設定為資料庫層級的全域暫存預存程序，並儲存在中 tempdb 。 全域臨時表和全域暫存預存程序會針對相同資料庫內的所有使用者會話共用。 來自其他資料庫的使用者會話無法存取全域臨時表。 如需詳細資訊，請參閱限定資料庫範圍的全域暫存資料表 (Azure SQL Database)。

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

若要深入瞭解 Azure SQL Database 中的 tempdb 大小，請參閱：

SQL 受控執行個體中的 tempdb

Azure SQL 受控執行個體以 SQL Server 的相同方式支援暫存物件，其中所有的全域臨時表和全域暫存預存程序都可供相同受控實例內的所有使用者會話存取。 同樣地，所有系統資料庫都可以存取。

若要深入瞭解 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 name AS FileName,
    size*1.0/128 AS FileSizeInMB,
    CASE 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 AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

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

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

SQL Server 中的 tempdb 效能改善

從 SQL Server 2016 (13.x) 開始，tempdb 效能已針對下列各方面進一步最佳化：

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

如需 tempdb 中效能改善的詳細資訊，請參閱部落格文章 TEMPDB - 檔案和追蹤旗標與更新！(英文)。

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

tempdb 中繼資料競爭一直以來都是在 SQL Server 上執行許多工作負載的可擴縮性瓶頸。 SQL Server 2019 (15.x) 引進了一個新功能，其為記憶體內部資料庫功能系列的一部分：經記憶體最佳化的 tempdb 中繼資料。

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

Azure SQL Database 或 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 系統檢視時，才適用這些限制。 如有需要，您可以在與存取使用者資料庫中經記憶體最佳化資料表相同的交易中建立暫存資料表。

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 檔案中所使用的磁碟空間：

 -- 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 的資源。

-- 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;

-- 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;

下一步