淺析 tempdb 在 SQL Server 系統中的關鍵角色與效能優化四大策略

tempdb 是 SQL Server 核心系統資料庫，所有使用者共享，承載臨時物件、內部運算、版本存放區。本文解析其特性、三大物件類型、優化四大策略 (最小化使用、預先配置空間、避免收縮、I/O 隔離)，並提供監控腳本。

tempdb 核心特性

三大物件類型

1. 使用者物件：臨時表 (#t, ##t)、資料表變數、游標。
2. 內部物件：排序/雜湊運算工作表、XML/LOB 處理、Service Broker 佇列。
3. 版本存放區：快照隔離/可讀次要複本的資料列版本。

優化四大策略

1. 最小化使用 tempdb

識別並減少以下高頻 tempdb 消費者：

• 臨時表 → 改用資料表變數或 CTE (小資料量)。
• DBCC CHECKDB → 排程在低峰期執行。
• 游標 → 改用集合式操作。
• CTE / 遞歸查詢 → 評估是否落地 tempdb。
• SORT_IN_TEMPDB (索引建立選項) → 權衡效能與空間。
• 線上索引重建 → 產生版本記錄佔用 tempdb。
• JOIN/彙總中間結果 → 適度索引減少溢寫。
• 觸發器 (AFTER/INSTEAD OF) → inserted/deleted 表在 tempdb。

2. 重新配置 tempdb 空間大小

• 預設 8MB + 10% 自動成長 → 大型負載下頻繁成長造成效能抖動。
• 建議：預先配置初始大小 (如 MDF 300MB, LDF 50MB)，觀察穩態佔用值設定。
• 執行：ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE=300MB)
• 啟用瞬間檔案初始化 (IFI) 加速成長。

3. 不要收縮 tempdb (除非必要)

• DBCC SHRINKFILE 會造成碎片、鎖定、效能下降。
• 若空間確為暫時性暴漲 (如一次性大報表)，可考慮離線維護窗口收縮。

4. I/O 隔離：tempdb 檔案與資料檔案分盤

• tempdb 高 I/O 特性 → 放置於最快儲存層 (NVMe/SSD/專用 RAID)。
• 資料檔 (.mdf) 與記錄檔 (.ldf) 分離不同實體磁碟。
• 多資料檔：依 CPU 核心數配置 (如 8 核 → 8 個 .ndf)，減少 SGAM/PFS 頁面爭用。
• 配置腳本：

  ALTER DATABASE tempdb ADD FILE (NAME='tempdev2', FILENAME='T:\tempdb2.ndf', SIZE=500MB);
  -- 重複至核心數

監控腳本：tempdb 空間使用量

SELECT
    a.name,
    SUM(size) * 8.0 / 1024 / 1024 AS [GB],
    SUM(size) * 8.0 / 1024 AS [MB]
FROM master.dbo.sysdatabases a
INNER JOIN master.dbo.sysaltfiles b ON a.dbid = b.dbid
WHERE a.name = 'tempdb'
GROUP BY a.name
ORDER BY 1;

總結

tempdb 是 SQL Server 的「高速草稿紙」，優化核心在於：減少不必要的寫入、預先配置避免成長抖動、物理隔離 I/O 爭用、多資料檔消除配置頁熱點。將 tempdb 納入每日巡檢與容量規劃，是穩定生產環境的必要條件。