SQL Server 2005 tempdb 變革深度解析：效能優化與監控新功能

前言

tempdb 是 SQL Server 系統資料庫，支援暫存表、表變數、工作表、版本存放區等內部運作。SQL Server 2005 對 tempdb 進行多項透明效能優化與監控增強，理解這些變化有助於撰寫高效 T-SQL 並進行容量規劃。

核心效能優化

1. 暫存表快取

• 建立暫存表（#temp）後，中繼資料快取，下次同樣結構重建極快
• 不快取條件：

1. 顯式 DDL（CREATE INDEX, ALTER TABLE）
2. 具名約束（CONSTRAINT PK_#temp PRIMARY KEY）
3. 動態 SQL 內建立（sp_executesql, EXEC()）

2. 工作表截斷優化

• 重複執行計畫中的工作表（Spool、Sort、Hash Join 等）
• 僅保留前 9 頁（72 KB），其餘釋放 → 降低 tempdb 空間壓力

3. 更多暫存物件快取

• 目錄項目不立即丟棄，LRU 策略定期清理

4. 記錄最小化

• 大幅降低 tempdb 交易記錄產生量與 I/O

5. 檔案比例填寫演算法

• 多檔案 tempdb 以比例填寫分配頁面（非輪詢）
• 每檔案依可用空間比例接收配置 → 減少 SGAM/PFS 爭用
• 建議： 每 CPU 核心 1 個 tempdb 資料檔案，置於不同磁碟/磁頭

6. 非同步捨棄

• DROP TABLE #temp 立即釋放鎖定，實體頁面釋放由背景執行緒處理
• 減少主執行緒等待

監控與診斷增強

1. 動態管理檢視 (DMV) 空間使用

-- tempdb 可用空間 (KB)
SELECT SUM(unallocated_extent_page_count) * 8 AS FreeSpaceKB
FROM sys.dm_db_file_space_usage
WHERE database_id = DB_ID('tempdb')

-- 版本存放區、使用者物件、內部物件空間細分
SELECT 
    SUM(user_object_reserved_page_count) * 8 AS UserObjKB,
    SUM(internal_object_reserved_page_count) * 8 AS InternalObjKB,
    SUM(version_store_reserved_page_count) * 8 AS VersionStoreKB,
    SUM(unallocated_extent_page_count) * 8 AS FreeKB
FROM sys.dm_db_file_space_usage
WHERE database_id = DB_ID('tempdb')

2. 追蹤旗標 1118 重構

• 舊版：DBCC TRACEON(1118) 強制統一區配置，減少 SGAM 爭用但增加空間浪費
• 2005：重構演算法，預設行為已優化，無需啟用 1118 即可獲得同等效果
• 可安全啟用 1118 進行追蹤，不再負面影響效能

3. 新效能計數器

實務開發指引

善用快取機制

• 預存程序中使用 #temp 而非動態 SQL 建表
• 避免具名約束、顯式 DDL 破壞快取
• 表變數（@table）同樣受惠快取，且無重編譯問題

降低 tempdb 爭用

• 大量中繼結果集考慮 INSERT ... SELECT 直寫永久表
• 避免大型排序/雜湊溢寫 tempdb（調整記憶體授予、索引支援）
• 版本存放區（快照隔離、觸發器、MARS、線上索引）監控空間成長

容量規劃公式

-- 粗略估算：每核心 1 檔案，單檔初始大小 = (預估峰值使用量 / 核心數) * 1.5
-- 監控 DMV 連續一週，取 95 百分位數作為基準

版本比較：2005 vs 2000

結語

SQL Server 2005 對 tempdb 的改進多為引擎層透明優化，無需修改應用程式即可受惠。但理解快取機制、記錄最小化、比例填寫與監控 DMV，能指導開發者：

1. 撰寫可快取的暫存物件模式
2. 進行科學的 tempdb 容量規劃
3. 利用 DMV 與計數器進行主動監控與疑難排解

這些隱藏的改進，正是區分「會用 SQL Server」與「精通 SQL Server」的關鍵。