tempdb 資料庫

適用於：SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric 中的 SQL 資料庫

本文介紹 tempdb 系統資料庫，這是一個全域資源，適用於所有連線到 SQL Server、Azure SQL Database 或 Azure SQL 受控實例中 Database Engine 實例的使用者。

Overview

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

• 明確建立的用戶物件。 其中包括：

  • 這些資料表上的全域或本地臨時資料表和索引
  • 暫存儲存程序
  • 大型變數，包括數據表變數
  • 數據表值函式中傳回的數據表
  • Cursors

  也可以在 tempdb中建立可在用戶資料庫中建立的用戶對象，不過這些物件是在沒有持久性保證的情況下建立的，而且會在 Database Engine 實例重新啟動時卸除。

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

  • 用於存儲工作流程、資料指標、排序和暫存大型物件 (LOB) 儲存的中繼結果的工作資料表。
  • 哈希比對或哈希匯總作業的工作檔案。
  • 建立或重建索引之類的作業 (若指定了 SORT_IN_TEMPDB) 或是特定 GROUP BY、ORDER BY 或 UNION 查詢的中繼排序結果。

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

• 版本會儲存，這是保存支援 數據列版本設定的數據列的數據列集合。 版本儲存庫包含：

  • 使用以資料列版本控管為基礎的 READ COMMITTED 或 SNAPSHOT 隔離級別交易的資料庫中，數據修改交易產生的資料列版本。
  • 由資料修改交易針對線上索引作業、Multiple Active Result Sets (MARS) 和 AFTER 觸發器等功能所產生的資料列版本。

  從 SQL Server 2025（17.x）開始，當啟用加速資料庫恢復（ADR）時tempdbtempdb，包含兩個不同且獨立的版本儲存庫：

  • 傳統版本存放區，用於尚未啟用 ADR 的用戶資料庫中由交易生成的資料列版本。
  • 持續性版本存放區 （PVS），用於 中 tempdb交易所產生的數據列版本。

  如果在tempdb中啟用了 ADR，請確保為tempdb數據檔配置足夠的磁碟空間，以包含這兩個版本存放區。 視您的工作負載而定，可能需要增加數據檔的大小 tempdb ，才能包含 PVS 數據。

  如需傳統版本存放區空間使用量的詳細資訊 tempdb ，請參閱 tempdb 中使用的空間。 如需 PVS 所用空間的詳細資訊，請參閱永續性版本存放區所使用的空間（PVS）。

tempdb 內的操作會以最小程度記錄。 每次啟動 Database Engine 時，都會重新建立 tempdb，讓系統一律以空 tempdb 資料庫啟動。 當建立暫存程序和本機暫存表的會話中斷連線時，這些程序和表格會自動刪除。

tempdb 從 Database Engine 的一個運行時間週期到另一個運行時間週期之間，永遠不會有任何資料需要儲存。 備份與還原操作不允許。tempdb

SQL Server 中 tempdb 的實體屬性

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

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

所有 tempdb 數據文件應該一律具有相同的初始大小和成長參數。

tempdb 資料檔數目

視 Database Engine 版本、其設定和工作負載而定，tempdb 可能需要多個數據檔來減輕配置爭用。

建議的數據檔總數取決於電腦上的邏輯處理器數目。 作為一般指引：

• 如果邏輯處理器數目小於或等於 8，請使用相同的數據檔數目。

• 如果邏輯處理器的數目大於八，則使用八個資料檔案。

• 如果仍然觀察到 tempdb 配置爭用狀況，請根據 4 的倍數增加數據檔案的數量，直到爭用減少到可接受的水平，或者變更工作負載。

如需詳細資訊，請參閱 建議，以減少 SQL Server tempdb 資料庫中的配置爭用。

若要檢查目前 tempdb的大小和成長參數，請使用 中的 tempdb 目錄檢視。

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

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

SQL Server 中於 tempdb 的資料庫選項

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

資料庫選項	預設值	可以修改
ACCELERATED_DATABASE_RECOVERY	OFF	Yes1
ALLOW_SNAPSHOT_ISOLATION	OFF	Yes
ANSI_NULL_DEFAULT	OFF	Yes
ANSI_NULLS	OFF	Yes
ANSI_PADDING	OFF	Yes
ANSI_WARNINGS	OFF	Yes
ARITHABORT	OFF	Yes
AUTO_CLOSE	OFF	No
AUTO_CREATE_STATISTICS	ON	Yes
AUTO_SHRINK	OFF	No
AUTO_UPDATE_STATISTICS	ON	Yes
AUTO_UPDATE_STATISTICS_ASYNC	OFF	Yes
AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN)	OFF	No
CHANGE_TRACKING	OFF	No
COMPATIBILITY_LEVEL	取決於 Database Engine 版本。 如需詳細資訊，請參閱 ALTER DATABASE （Transact-SQL） 相容性層級。	Yes
CONCAT_NULL_YIELDS_NULL	OFF	Yes
CONTAINMENT	NONE	No
CURSOR_CLOSE_ON_COMMIT	OFF	Yes
CURSOR_DEFAULT	GLOBAL	Yes
資料庫狀態	ONLINE	No
資料庫更新	READ_WRITE	No
資料庫使用者存取	MULTI_USER	No
DATE_CORRELATION_OPTIMIZATION	OFF	Yes
DB_CHAINING	ON	No
DELAYED_DURABILITY	DISABLED 不論此選項為何，延遲持久性一律會在 上 tempdb。	Yes
ENCRYPTION	OFF	No
MIXED_PAGE_ALLOCATION	OFF	No
NUMERIC_ROUNDABORT	OFF	Yes
PAGE_VERIFY	新安裝的 SQL Server CHECKSUM 當 SQL Server 實例就地升級時，可能會保留現有的 PAGE_VERIFY 值。	Yes
PARAMETERIZATION	SIMPLE	Yes
QUOTED_IDENTIFIER	OFF	Yes
READ_COMMITTED_SNAPSHOT	OFF	No
RECOVERY	SIMPLE	No
RECURSIVE_TRIGGERS	OFF	Yes
服務代理	ENABLE_BROKER	Yes
TARGET_RECOVERY_TIME	60	Yes
TEMPORAL_HISTORY_RETENTION	ON	Yes
TRUSTWORTHY	OFF	No

¹ 從 SQL Server 2025（17.x）開始，支援在 tempdb 將 ACCELERATED_DATABASE_RECOVERY 設定為 ON。 在先前版本的 SQL Server 中，不允許修改 ACCELERATED_DATABASE_RECOVERY 資料庫的選項 tempdb 。

關於這些資料庫選項的說明，請參見 ALTER DATABASE SET 選項。

Azure SQL 資料庫 中的 tempdb

在 Azure SQL Database 中，tempdb 行為和設定的某些層面與 SQL Server 不同。

對於單一資料庫，邏輯伺服器上的每個資料庫都有自己的 tempdb。 在彈性池中， 是 tempdb 同一池中所有資料庫的共享資源，但由一個資料庫建立的臨時物件不會被同一彈性池中其他資料庫看到。

tempdb中的物件，包括目錄檢視和動態管理檢視 （DMV），可透過 tempdb 資料庫的跨資料庫參考來存取。 例如，您可以查詢 sys.database_files 檢視：

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

Azure SQL Database 中的全域臨時表 受限於資料庫。 如需詳細資訊，請參閱 azure SQL Database 中資料庫範圍全域臨時表。

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

• vCore 購買模型：單一資料庫、集區資料庫
• DTU 購買模型：單一資料庫、集區資料庫

SQL 受控執行個體中的 tempdb

在 Azure SQL 受控實例中，tempdb 行為和預設設定的某些層面與 SQL Server 不同。

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

Azure SQL 受控實例支援與 SQL Server 相同的暫存物件，其中所有全域臨時表和全域暫存程式都可由相同 SQL 受控實例內的所有用戶會話存取。

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

Fabric 中 SQL 資料庫中的 tempdb

欲了解更多關於 tempdb Microsoft Fabric SQL 資料庫大小的資訊，請參閱功能 比較：Azure SQL 資料庫與 Microsoft Fabric 中的 SQL 資料庫中的資源限制章節。

與 Azure SQL 資料庫類似， Microsoft Fabric 中 SQL 資料庫 中的全域暫存資料表是資料庫範圍的。 如需詳細資訊，請參閱 azure SQL Database 中資料庫範圍全域臨時表。

局限性

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

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

Permissions

任何使用者都可以在 tempdb 中建立暫存物件。

除非使用者收到其他許可權，否則使用者只能在 tempdb中存取自己的非暫存物件。

撤銷CONNECTtempdb 許可權，以防止資料庫使用者或角色使用 tempdb。 不建議這麼做，因為許多作業需要使用 tempdb。

最佳化 SQL Server 中的 tempdb 效能

tempdb 檔案的大小和實體位置可能會影響效能。 例如，如果初始大小 tempdb 太小，則每次重新啟動資料庫引擎實例時，可能會需要時間和資源來自動將 tempdb 成長到支援工作負載所需的大小。

• 可能的話，請使用 立即檔案初始化 來改善數據檔成長作業的效能。
  • 從 SQL Server 2022 （16.x）開始，交易記錄檔的成長事件，大小達到 64 MB 以下，也可以受益於即時檔案初始化。 如需詳細資訊，請參閱 立即檔案初始化和事務歷史記錄。
• 您可將檔案大小設定為夠大的值來容納環境中的典型工作負載，藉此為所有 tempdb 檔案預先配置空間。 預先配置可防止 tempdb 太常自動成長，這可能會對效能造成負面影響。
• tempdb 資料庫中的檔案應該設定為自動成長，以在非計劃性成長事件期間提供空間。
• 將 tempdb 分割成多個大小相等的數據檔，可以提升使用 tempdb的作業效率。
  • 為了避免數據配置不平衡，數據文件應該具有相同的初始大小和成長參數，因為 Database Engine 會使用比例填滿演算法，偏好在具有更多可用空間的檔案中配置。
  • 將檔案成長增量設定為合理的大小，例如 64 MB，讓所有數據檔的成長增量都相同，以避免成長不平衡。
• 從 SQL Server 2025（17.x）開始，考慮啟用加速資料庫恢復tempdb，以獲得即時交易回滾及積極截斷tempdb日誌的好處。 如需詳細資訊，請參閱 tempdb 中的 ADR。
  • 啟用或停用 中的 tempdb ADR 需要重新啟動 Database Engine 才會生效。

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

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST (IIF (max_size = 0, 0, 1) AS BIT) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0 THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1 THEN 'Percent'
       END AS growth_increment_value_unit
FROM tempdb.sys.database_files;

將 tempdb 資料庫放在快速的 I/O 子系統上。 除非您遇到磁碟層級 I/O 瓶頸，否則個別數據檔或 tempdb 數據檔群組不一定需要位於不同的磁碟上。

如果 和 使用者資料庫之間 tempdb 有 I/O 爭用，就把檔案放 tempdb 到和使用者資料庫使用的磁碟不同的磁碟上。

Note

為了改善效能，即使資料庫選項 設定為 tempdb，DELAYED_DURABILITY 一律會在 DISABLED 上啟用。 因為啟動時會重新建立 tempdb，所以不會經歷復原過程，也不會提供持久性保證。

SQL Server 中 tempdb 的改善措施

於 SQL Server 2025（17.x）中引入

• Tempdb 空間資源控管會對應用程式或使用者工作負載所耗用的總 tempdb 空間量強制執行限制。 這樣可以改善可靠性，並避免中斷，方法是防止失控的查詢或工作負載在tempdb中耗用大量空間。 如需詳細資訊，請參閱 Tempdb 空間資源治理。
• 加速資料庫復原在tempdb中提供支援，為tempdb中的交易提供即時交易回復和積極的記錄截斷。

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

• 引進 改善的延展性，並透過系統頁面閂鎖並行增強功能。 並發更新全域配置對應 (GAM) 頁面和共用全域配置對應 (SGAM) 頁面，減少配置/解除配置資料頁面和範圍時的頁面閂鎖爭用。 這些增強功能適用於所有使用者資料庫，特別有利於 tempdb中的繁重工作負載。 欲了解更多關於GAM與SGAM頁面的資訊，請參閱《內情揭密：GAM、SGAM及PFS頁面》。 如需詳細資訊，請參閱系統頁面閂鎖並行增強功能 (Ep. 6) | 公開的資料。

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

• 資料庫引擎在開啟 FILE_FLAG_WRITE_THROUGH 檔案以允許最大磁碟輸送量時，不會使用 [tempdb] 選項。 由於啟動時會重新建立 tempdb，因此不需要此選項來提供數據持久性。 如需 FILE_FLAG_WRITE_THROUGH 的詳細資訊，請參閱記錄和資料儲存演算法，以擴充 SQL Server 中的資料可靠性。
• 記憶體最佳化 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 2017 (14.x) 中推出

• SQL 設定體驗可改善初始 tempdb 檔案配置的指導方針。 SQL 設定會在初始檔案大小設定超過 1 GB 且未啟用 即時檔案初始化 時提醒客戶，防止實例啟動延遲。
• sys.dm_tran_version_store_space_usage 動態管理檢視會追蹤每個資料庫的版本存放區使用量。 此 DMV 適用於想要根據每個資料庫版本存放區使用量需求主動規劃 tempdb 大小調整的 DBA。
• 智慧查詢處理 調適型聯結和記憶體授與回饋等功能，可減少查詢連續執行的記憶體溢出，減少 tempdb 資源使用率。

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

• 暫存資料表和資料表變數會被快取。 快取可讓刪除及建立暫時物件的作業以極快的速度執行。 快取也可以減少頁面分配和中繼資料爭用。
• 已改進配置頁面鎖存協議，以減少使用的 UP (更新) 鎖存數量。
• tempdb 的記錄負荷已縮減，以降低 tempdb 記錄檔的磁碟 I/O 頻寬耗用量。
• SQL 安裝程式會在新的實例安裝期間新增多個 tempdb 數據檔。 請檢閱建議，並在 SQL 安裝程式的 tempdb 頁面中設定您的 ，或使用命令行參數 /SQLTEMPDBFILECOUNT。 根據預設，SQL 安裝程式會新增與邏輯處理器數目相同的 tempdb 數據檔，或新增 8 個，以較低者為準。
• 如果有多個 tempdb 資料檔案，則視成長設定而定，所有檔案都會同時以相同數量自動成長。 不再需要追蹤旗標 1117。 如需詳細資訊，請參閱 TEMPDB 和使用者資料庫的 -T1117 和 -T1118 變更。
• tempdb 中的所有配置都使用統一範圍。 不再需要追蹤旗標 1118。 如需tempdb效能提升的詳細資訊，請參閱部落格文章TEMPDB - 檔案和追蹤標誌及更新，天哪！。
• AUTOGROW_ALL_FILES 屬性在 PRIMARY 檔案群組中一律會被啟用。

記憶體優化的 TempDB 元數據

歷來，暫存物件元數據爭用一直是許多 SQL Server 工作負載擴展性的瓶頸。 為了解決這個問題，SQL Server 2019 （15.x） 引進了 記憶體內部資料庫 功能系列：記憶體優化 TempDB 元數據的功能。

啟用記憶體優化的TempDB元數據功能，會針對先前受限於 tempdb內的暫存物件元數據競爭所限制的工作負載，移除此瓶頸。 從 SQL Server 2019 （15.x）開始，管理暫存物件元數據所涉及的系統數據表可能會變成無閂鎖、非持久、記憶體優化的數據表。

Tip

由於目前存在 限制，建議僅在對象元數據爭用發生並對工作負載產生重大影響時，才啟用記憶體優化的 TempDB 元數據。

如果發生暫存物件元數據爭用，下列診斷查詢會傳回一或多個數據列。 每個數據列都代表 系統數據表，並傳回在執行此診斷查詢時爭奪該數據表存取權的會話數目。

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT (r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

請觀看這段七分鐘的影片，以獲得關於如何以及何時使用記憶體優化 TempDB 元數據功能的概觀。

Note

目前，記憶體優化的 TempDB 元資料功能尚未出現在 Azure SQL 資料庫、Microsoft Fabric 的 SQL 資料庫以及 Azure SQL 管理實例中。

設定及使用記憶體優化的TempDB元數據

下列各節包含啟用、設定、驗證和停用記憶體優化TempDB元數據功能的步驟。

Enable

若要啟用此功能，請使用下列文稿：

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

如需詳細資訊，請參閱 ALTER SERVER。 這項設定變更需要重新啟動服務才會生效。

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

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

如果傳回的值是 1，且啟用此功能之後發生重新啟動，則會啟用此功能。

如果在啟用記憶體優化 TempDB 元數據後，伺服器因任何原因無法啟動，您可以使用 啟動選項，透過以最小組態 -f，來略過此功能。 然後，您可以 停用 功能，並移除 -f 選項，以正常模式重新啟動 Database Engine。

系結至資源集區以限制記憶體使用量

若要保護伺服器免於潛在的記憶體不足狀況，我們建議您將 tempdb 系結至資源管理員 資源集區， 限制記憶體優化 TempDB 元數據所耗用的記憶體。 下列範例腳本會建立資源集區，並將其記憶體上限設定為 20%、啟用 資源管理員，並將 tempdb 系結至資源集區。

此範例使用 20% 作為示範用途的記憶體限制。 根據您的工作負載而定，環境中的最佳值可能較大或更小，而且如果工作負載變更，可能會隨著時間而變更。

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

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

即使已啟用記憶體優化TempDB元數據，這項變更也需要重新啟動服務才會生效。

確認資源集區系結並監視記憶體使用量

若要確認 tempdb 系結至資源集區並監視集區的記憶體使用量統計數據，請使用下列查詢：

; WITH resource_pool
AS (SELECT p.pool_id,
           p.name,
           p.max_memory_percent,
           dp.max_memory_kb,
           dp.target_memory_kb,
           dp.used_memory_kb,
           dp.out_of_memory_count
    FROM sys.resource_governor_resource_pools AS p
         INNER JOIN sys.dm_resource_governor_resource_pools AS dp
             ON p.pool_id = dp.pool_id)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
     LEFT OUTER JOIN resource_pool AS rp
         ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

拿掉資源集區系結

若要在啟用記憶體優化TempDB元數據的同時移除資源集區系結，請執行下列命令並重新啟動服務：

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Disable

若要停用記憶體優化的 TempDB 元數據，請執行下列命令並重新啟動服務：

ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

記憶體優化TempDB元數據的限制

• 啟用或停用記憶體優化的TempDB元數據功能需要重新啟動。

• 在某些情況下，您可能會發現 MEMORYCLERK_XTP 記憶體管理員的高記憶體使用情況導致工作負載發生記憶體不足錯誤。

  若要查看 MEMORYCLERK_XTP 記憶體管理器的記憶體使用量，其中與所有其他記憶體管理器和目標伺服器記憶體的相對使用量，請執行下列查詢：

  SELECT SUM(IIF (type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
         SUM(IIF (type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
         SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
  FROM sys.dm_os_memory_clerks
      CROSS JOIN sys.dm_os_sys_info;
  

  如果 MEMORYCLERK_XTP 記憶體使用率過高，您可以如以下所示緩解此問題：

  • 將 tempdb 資料庫綁定至一個資源集區，以限制記憶體優化的 TempDB 元數據的記憶體耗用量。 如需詳細資訊，請參閱 配置與使用記憶體優化的 tempdb 元資料。
  • 系統預存程式可以定期執行，以釋放不再需要 MEMORYCLERK_XTP 記憶體。 如需詳細資訊，請參閱 sys.sp_xtp_force_gc (Transact-SQL)。

  如需詳細資訊，請參閱 記憶體最佳化 tempdb 元資料（HkTempDB）記憶體不足錯誤。

• 當你使用 In-Memory OLTP 時，單一交易不允許存取多個資料庫中記憶體優化的資料表。 因此，任何涉及使用者資料庫中記憶體最佳化資料表的讀取或寫入交易，都無法在同一交易中存取系統檢視 tempdb。 如果發生這種情況，您會收到錯誤 41317：

  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.
  

  這項限制也適用於其他案例，其中單一交易嘗試存取多個資料庫中的記憶體優化數據表。

  例如，如果您在包含記憶體優化數據表的使用者資料庫中查詢 sys.stats 目錄檢視，您可能會收到錯誤 41317。 這是因為查詢會嘗試存取 統計數據， 用戶資料庫中記憶體優化數據表的數據，以及 tempdb中的記憶體優化元數據。

  啟用記憶體優化 TempDB 元數據時，下列範例腳本會產生此錯誤：

  BEGIN TRAN;
  
  -- Create an In-memory OLTP transaction that accesses a system view in tempdb
  SELECT name
  FROM tempdb.sys.tables;
  
  -- An attempt to create an In-memory OLTP transaction in the user database fails
  INSERT INTO <user database>.<schema>.<memory-optimized table>
  VALUES (1);
  
  COMMIT TRAN;
  

  Note

  此限制不適用於臨時資料表。 您可以在存取使用者資料庫中記憶體優化數據表的相同交易中建立臨時表。

• 針對系統目錄檢視的查詢一律使用 READ COMMITTED 隔離等級。 啟用記憶體優化 TempDB 元數據時，tempdb 中的系統目錄檢視查詢會採用 SNAPSHOT 隔離等級。 無論哪種情況，鎖提示都不會被遵守。

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

  • 因此，當啟用記憶體優化的 TempDB 元資料時，不支援使用含有 COLUMNSTORE 或 COLUMNSTORE_ARCHIVE 資料壓縮參數的 sp_estimate_data_compression_savings 系統儲存過程。

SQL Server 中 tempdb 的容量規劃

判斷 tempdb 的適當大小取決於許多因素。 這些因素包括使用的工作負載和 Database Engine 功能。

建議您在可重現一般工作負載的測試環境中執行下列工作，以分析 tempdb 空間耗用量：

• 为 文件启用tempdb。 所有 tempdb 數據檔都應該具有相同的初始大小和自動成長設定。
• 重現工作負載並監視空間使用 tempdb。
• 如果您使用定期 索引維護，請執行維護作業並監視 tempdb 空間。
• 使用先前步驟中已使用的最大空間值來預測工作負載使用量總計。 針對預計的並行活動調整此值，然後據以設定 tempdb 的大小。

監視 tempdb 使用

tempdb 中的磁碟空間不足可能會導致重大中斷和應用程式停機。 您可以使用 sys.dm_db_file_space_usage 動態管理檢視來監視 tempdb 檔案中使用的空間。

例如，下列範例文本會尋找：

• tempdb 中的可用空間（不考慮可能為 tempdb 增長而可用的磁碟空間）。
• 傳統版本存放區所使用的空間。
  • 若要在加速資料庫復原 (ADR) 啟用時監視永續性版本存放區 (PVS) 的大小，請參閱tempdb。
• 內部物件所使用的空間。
• 用戶物件所使用的空間。

SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

若要在會話或任務層級監控 tempdb 中的頁面分配或解除分配活動，您可以使用 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage 動態管理檢視。 這些檢視可協助您識別使用大量 tempdb 空間的查詢、臨時表或數據表變數。

例如，使用下列範例腳本來取得每個會話中所有目前執行的任務中內部物件所配置和解除配置的 tempdb 空間：

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空間，適用於執行與已完成的任務。

; WITH tempdb_space_usage
AS (SELECT session_id,
           request_id,
           user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
           user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
    FROM sys.dm_db_task_space_usage
    UNION ALL
    SELECT session_id,
           NULL AS request_id,
           user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
           user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
    FROM sys.dm_db_session_space_usage)
SELECT session_id,
       COALESCE (request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

相關內容

• SORT_IN_TEMPDB 選項用於索引
• 系統資料庫
• sys.databases
• sys.master_files
• 移動資料庫檔案