索引類型選擇決策樹
| 場景 | 推薦索引 | 理由 |
|---|
| 高選擇性、範圍查詢、順序存取、外鍵關聯 | 叢集索引 | 資料實際排序，範圍掃描極快，每表僅一個 |
| 低選擇性、精確查詢、多條件組合、覆蓋索引需求 | 非叢集索引 | 最多 249 個，葉層含書籤指向資料 |
| 主鍵 (PK) | 預設建立**唯一叢集索引** | 強制唯一 + 排序；可改為非叢集若有更適合叢集的欄位 |
| 外鍵 (FK) 高頻 JOIN | 非叢集索引 | 加速 JOIN、避免 Table Scan |
資料頁、填充因子與頁面分裂
- 資料頁 (8KB)：索引建立時依 Fill Factor 預留空間。
- Fill Factor (0-100%)**：
0 = 100% (預設)。頁面填滿觸發**頁面分裂** → 邏輯/物理順序偏離 → 碎片 → 效能下降。
- 動態調整不可行：Fill Factor 僅在建立/重建時生效，需重建索引才能變更。
Fill Factor 設定指引
| 工作負載特徵 | 建議 Fill Factor | 說明 |
|---|
| 唯讀/極少修改、遞增鍵 (Identity/日期) | 100% (0) | 最大化密度，無分裂風險 |
| 高頻 INSERT/UPDATE、遞增鍵 | 90-95% | 預留少量空間吸收尾部插入 |
| 高頻修改、隨機鍵 (GUID/雜湊) | 70-85% | 大幅預留空間減少隨機位置分裂 |
| 大量批次匯入後轉唯讀 | 100% → 匯入後 REBUILD 100% | 匯入時無索引/低 Fill Factor，匯入後重建 |
碎片整理：DBCC INDEXDEFRAG vs DBREINDEX (SQL 2000) / REORGANIZE vs REBUILD (2005+)
| 指標 | REORGANIZE / INDEXDEFRAG | REBUILD / DBREINDEX |
|---|
| 線上/離線 | 線上 (無鎖) | 離線 (Sch-M 鎖) / Enterprise 版支援 ONLINE=ON |
| 碎片清除徹底度 | 部分 (葉層重排) | 完全 (重建 B-Tree) |
| 統計資料更新 | 否 | 是 (全掃描) |
| 記錄檔空間 | 小 (分批) | 大 (完整重建) |
| 適用碎片閾值 | 10-30% | > 30% |
智慧型維護腳本邏輯 (Ola Hallengren 風格)
IF fragmentation > 30% → ALTER INDEX ... REBUILD WITH (FILLFACTOR = ..., ONLINE = ON)
ELSE IF fragmentation > 10% → ALTER INDEX ... REORGANIZE
ELSE → SKIP
-- 完成後若 REORGANIZE 或 SKIP 且統計資料過舊 → UPDATE STATISTICS ... WITH FULLSCAN
統計資料：查詢優化器的眼睛
DBCC SHOW_STATISTICS / sys.dm_db_stats_properties 檢查 rows, rows_sampled, modification_counter- 自動更新觸發條件：資料列變更 > 20% + 500 (SQL 2008R2 SP1+ 引入動態閾值)
- 手動更新：
UPDATE STATISTICS dbo.TableName INDEX_Name WITH FULLSCAN (重要索引) / SAMPLE (大表)
- 非同步更新：
AUTO_UPDATE_STATISTICS_ASYNC ON 避免查詢等待統計更新
資料庫維護計畫 (DMPW) 自動化
- EM → 管理 → **資料庫維護計畫** → 嚮導建立
- 核心任務勾選：
☑ **重新組織資料與資料頁** (REORGANIZE + 更新統計)
☑ **重建索引** (可指定 Fill Factor、排程)
☑ **更新統計資料** (獨立排程，高頻)
☑ **檢查資料庫完整性** (DBCC CHECKDB)
☑ **備份資料庫/記錄檔**
- 輸出為 **SQL Server Agent 作業**，可進階編輯步驟、通知、排程
結語
索引優化非一次性動作，而是 **「選擇正確類型 → 設定合適 Fill Factor → 監控碎片/統計 → 自動化 REORGANIZE/REBUILD/UPDATE STATS」** 的持續週期。掌握決策矩陣、閾值與自動化工具，讓索引持續保持「優化器最愛」的健康狀態。
