tempdb 資料庫

適用于:SQL Server (所有支援的版本) Azure SQL Database Azure SQL 受控執行個體

本文說明 tempdb 系統資料庫、所有連線至 SQL Server實例的使用者、Azure SQL資料庫或Azure SQL 受控執行個體的全域資源。

概觀

系統 tempdb 資料庫是一種全域資源,可保留:

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

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

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

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

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

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

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

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 新安裝 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 資料庫中 tempdb 的行為與 Azure VM 上的行為SQL Server、Azure SQL 受控執行個體和SQL Server不同。

SQL Database 中的 tempdb

Azure SQL 資料庫中的單一和集區資料庫支援全域臨時表和範圍設定為資料庫層級的全域暫存程式,並儲存在 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 大小太小,系統處理負載的一部分可能會佔用自動成長 tempdb 到每次重新開機 SQL Server實例時支援工作負載所需的大小。

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

您可將檔案大小設定為夠大的值來容納環境中的典型工作負載,藉此為所有 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 系統預存程序搭配 COLUMNSTORECOLUMNSTORE_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_usagesys.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;

下一步