Tempdb 對 SQL Server 效能的影響與優化策略全解析

Tempdb 基礎知識回顧

定位與角色

Tempdb 是 SQL Server 實例層級的全域共享資源，所有連線共用同一個 Tempdb。儲存三類物件：

• 使用者物件：臨時表 (#, ##)、表變數、資料表值參數、游標工作表
• 內部物件：排序/雜湊中繼結果、Service Broker、XML 變數剖析、索引線上重建排序暫存
• 版本存放區：快照隔離/可讀次要複本的列版本

核心特性

1. 非持久化：SQL Server 重啟時依 model 重建，不需復原。
2. 恢復模式固定為 SIMPLE，檢查點後記錄標記可重用，不可變更。
3. 單一檔案群組 (PRIMARY)，不可新增檔案群組。

Tempdb 運作機制

• 啟動時依 model 建立：預設 MDF 8 MB、LDF 1 MB、自動成長 10%。
• 活動頻繁時自動成長；SIMPLE 模式下記錄持續截斷。
• 臨時物件 (臨時表、表變數、游標) 在批次/程序結束自動清除。

優化策略四大支柱

3.1 極小化 Tempdb 使用

識別高頻使用點並評估替代方案：

3.2 預先配置適當初始大小

預設 8 MB 遠低於生產負載需求，自動成長 10% 導致頻繁擴展、碎片化、I/O 停頓。建議：

• 觀察尖峰期 Tempdb 實際佔用穩定值，設為初始大小 (如 MDF 300 MB、LDF 50 MB)。
• 使用 ALTER DATABASE 修改，或安裝精靈/SSMS 圖形介面設定。
• 多資料檔案時，每個檔案同樣預先配置，避免比例填充演算法失效。

3.3 避免無謂收縮

DBCC SHRINKFILE 釋放空間後，後續負載仍會觸發自動成長，產生「縮-長」震盪、碎片化、鎖競爭。原則：除非檔案異常膨脹且確認不再需要，否則勿縮減。

3.4 I/O 隔離與多檔案配置

• Tempdb 資料/記錄檔放置於專用高效能儲存 (NVMe/SSD/專用 LUN)，與使用者資料庫分離。
• 資料檔案數 = 邏輯 CPU 核心數 (≤ 8) 或 8 (> 8 核心)，所有檔案相同大小、成長量，啟用比例填充。
• 啟用追蹤旗標 1117/1118 (SQL 2016+ 內建預設行為) 強制統一區分配、同步成長，消除 SGAM/PFS 爭用。

常見誤區釐清

• ❌ 「Tempdb 只在記憶體」——資料頁同樣受緩衝池管理，壓力大時會寫入磁碟。
• ❌ 「表變數不寫 Tempdb」——表變數同樣儲存於 Tempdb，只是統計資訊與重編譯行為不同。
• ❌ 「多檔案一定快」——若負載非分配爭用主導 (如大量排序溢寫)，單檔案於高速儲存可能更優。

總結

Tempdb 優化非單一動作，而是負載特徵分析 → 瓶頸定位 → 針對性配置的迭代過程。關鍵指標監控：PAGEIOLATCH_*、PAGELATCH_UP (SGAM/PFS/GAM)、Tempdb 資料檔案大小/成長頻率、版本存放區大小。結合 Query Store / Extended Events 識別高 Tempdb 耗用查詢，從源頭優化查詢計畫，才是治本之道。