系統資料庫概覽
SQL Server 四大核心系統資料庫：
- master：儲存實例層級中繼資料、端點、連結伺服器、系統設定，若損壞將導致無法啟動
- model：所有新資料庫的範本，tempdb 每次啟動都依此重建
- msdb：SQL Server Agent 排程、警示、作業儲存
- tempdb：全域共享資源，儲存暫存表、暫存程序、表變數、游標、中間結果集、排序作業等
tempdb 運作機制
- SQL Server 重啟時自動 Drop → Re-create
- 依據 model 資料庫預設建立：MDF 8MB、LDF 1MB、自動成長 10%、Simple 復原模式
- Simple 模式下交易記錄自動截斷，最小化記錄開銷
三大優化策略
1. 最小化 tempdb 使用
常見使用 tempdb 的情境：
| 情境 | 建議 |
|---|
| 使用者建立暫存表 | 能避免則避免；大量資料且頻繁存取時加索引 |
| DBCC CHECKDB 等維護作業 | 排程在低峰期執行 |
| 游標 | 嚴重影響效能，盡量改用集合式操作 |
| CTE (Common Table Expression) | 評估是否必要，複雜 CTE 會大量使用 tempdb |
| SORT_IN_TEMPDB 選項建立索引 | 權衡效能與 tempdb 壓力 |
| 線上索引重建 | 會使用 tempdb 儲存對應關係 |
| 聯結產生的中間結果集 | 優化查詢計畫，減少不必要的大型聯結 |
| 排序作業 | 確保統計資料正確，避免記憶體授予不足溢寫 tempdb |
| AFTER/INSTEAD OF 觸發器 | 觸發器內部使用工作表儲存 inserted/deleted |
2. 重新規劃 tempdb 空間大小
核心問題：預設 8MB 起始大小 + 10% 自動成長，大型資料庫頻繁成長會造成嚴重效能抖動。
解決方案：
- 預先設定適當的初始大小（如 MDF 300MB、LDF 50MB）
- 可透過 SSMS 圖形介面或
ALTER DATABASE 修改
- 觀察尖峰期 tempdb 實際使用量，設定略大於該值的初始大小
3. 避免不必要的 SHRINK 操作
tempdb 顯示大量可用空間時，不要輕易執行 SHRINK：
- 後續負載增加時仍會觸發自動成長，造成效能抖動
- SHRINK 本身會產生碎片、消耗資源
- 只有在確定不再需要該空間（如資料庫搬移、永久降低負載）才考慮
4. 檔案與 I/O 隔離
- 將 tempdb 放置於高效能儲存裝置（SSD/NVMe）
- 與使用者資料庫檔案分離至不同磁碟/磁碟陣列
- 資料檔案數量建議 = 邏輯 CPU 核心數（最多 8 個），減少配置爭用
- 所有資料檔案設定相同大小與成長增量，啟用比例填充演算法
監控指標
sys.dm_db_file_space_usage：監控 tempdb 空間使用sys.dm_db_session_space_usage：找出佔用最多 sessionsys.dm_db_task_space_usage：任務層級空間使用- Performance Counter：Temp Tables Creation Rate、Temp Tables For Destruction
結語
tempdb 是 SQL Server 效能的關鍵樞紐。透過預先配置空間、隔離 I/O、減少不必要使用、建立多資料檔案，能顯著降低爭用並提升整體吞吐量。持續監控、定期調整，才能維持最佳狀態。
