前言
SQL Server 效能優化是系統工程，單點調優效果有限。本文歸納六大支柱，涵蓋儲存、TempDB、熱點、資料型別、碎片、檔案架構，提供可落地的配置指引。
支柱一：資料與記錄檔分離儲存
- 資料檔 (隨機讀寫) + 索引 ↔ 記錄檔 (循序寫入) 放置於**不同物理磁碟/RAID 陣列**
- 效益：I/O 平行化、記錄檔順序寫入無干擾、災難恢復支援尾部記錄備份
- 實作：建庫時指定
FILENAME 路徑；既有庫用 ALTER DATABASE MODIFY FILE + 離線/線上移動
支柱二：TempDB 獨立高速儲存
- TempDB 承載：暫存表、表變數、排序溢出、版本存放區 (快照隔離)、游標工作表
- 爭用嚴重時成為系統瓶頸
- 建議：
- 放置於**獨立 SSD/NVMe 或 RAID 10**
- 預先配置足夠空間，**關閉自動增長**或設固定 MB 增長 (避免頻繁擴展鎖爭用)
- 多資料檔：邏輯 CPU 核心數 ≤ 8 時同核心數，>8 時從 8 開始每 4 核心加 1 檔，均勻大小 (減少 SGAM/PFS 爭用)
- 啟用瞬間檔案初始化
支柱三：避免熱點資料 (Last-Page Insert Contention)
- 舊版 (≤7.0) 堆表插入集中於物理末尾 → 熱頁鎖爭用
- 新版 (≥2005) PFS/GAM 頁面分配演算法已分散插入點
- **設計禁忌**：避免在**自然遞增欄位** (身分證號、時間戳、Identity) 上建立**叢集索引**作為主鍵，若業務需求必須，考慮：
- 雜湊分區 (Hash Partitioning)
- 反轉鍵值索引 (Computed column + REVERSE)
- In-Memory OLTP (記憶體優化表)
支柱四：資料型別精簡 (窄表策略)
- 單頁 8KB (可用 8060B)，列越窄 → 每頁容納列數越多 → 掃描頁數越少 → I/O 越少
- 實務原則：
- 定長優於變長 (char vs varchar) — 固定長度避免行遷移
- 最小足夠型別：
tinyint (0-255) 替代 int，smallmoney 替代 money，smalldatetime 替代 datetime
- 避免
MAX 型別 (varchar(max), varbinary(max)) 除非必要 — 可能溢出行外存儲
- 刪除未用欄位，規範化拆分大欄位至副表
支柱五：監控與整理空間碎片
- 自動增長產生**外部碎片** (檔案在磁碟不連續) → 循序讀取變隨機 I/O
- 索引內部碎片 (頁分裂/邏輯順序≠物理順序) → 掃描效率降低
- 維護計畫：
- 索引重建
ALTER INDEX ... REBUILD (線上/離線) — 修復內外部碎片、更新統計資料
- 索引重組
ALTER INDEX ... REORGANIZE — 輕量、線上、適用碎片 10-30%
- 統計資料更新
UPDATE STATISTICS / sp_updatestats — 優化器決策依據
- 檔案碎片整理：Windows 層級排程 (非 SQL 服務時間)
支柱六：主檔案與次要檔案架構 (檔案群組策略)
- 每庫一主檔案 (Primary Filegroup) 含系統物件
- 小型庫 (<1GB) 單檔案足矣
- 中大型庫建議：
- 主檔案群組：僅存系統表、小型查閱表
- 使用者檔案群組 (User Filegroup)：**設為預設**，存使用者表、索引
- 可進一步分離：
DATA_FG (資料) + INDEX_FG (索引) 放不同磁碟
- 唯讀/歸檔資料放
READONLY_FG，備份策略分級 (唯讀檔案群組僅需備份一次)
綜合配置清單
| 層面 | 關鍵動作 | 驗收指標 |
|---|
| 儲存 | 資料/記錄/TempDB 三分離 | 磁碟隊列長度 < 2、延遲 < 10ms |
| TempDB | 多檔案、固定大小、瞬間初始化 | PFS/SGAM 爭用等待 < 1ms |
| 熱點 | 避免遞增鍵叢集索引 | PAGEIOLATCH/PAGELATCH_EX 等待極低 |
| 型別 | 窄表設計、最小足夠型別 | 平均列寬 < 100B、頁密度 > 90% |
| 碎片 | 週重建/日重組、統計更新 | 掃描頁數穩定、計畫無退化 |
| 檔案群組 | 使用者物件隔離、唯讀分離 | 備份時間縮減 50%+、還原彈性 |
總結
六大支柱環環相扣：儲存奠基、TempDB 解壓、熱點消除、型別精簡、碎片管控、架構分層。建議納入新專案架構評審清單，既有系統按優先級逐項改善，並建立持續監控儀表板。
