SQL Server 資料庫效能優化綜合指南：設計、硬體、索引、查詢與監控

一、資料庫設計層面

1.1 正規化與反正規化平衡

• 前三正規化 (1NF/2NF/3NF)：消除冗余、減少頁數、利於維護
• 適度反正規化提升效能：
  ① 多表聯結 (>4 路) → 增加冗余欄位
  ② 常用計算欄位 (總計、最大值) → 實體化儲存 + 觸發器維護一致性
  ③ 垂直分表：熱/冷欄位分離 (PK 複製)
  ④ 水平分表：歷史/現行資料分離、依邏輯分區 (部門/地區)

1.2 物理實作策略

• 資料型別最小化：SMALLINT 代替 INT、VARCHAR(20) 代替 VARCHAR(100) → 索引更窄、單頁容納更多列、I/O 更少
• 檔案群組/磁碟分離：
  表 → 檔案群組 A (磁碟 1)
  非叢集索引 → 檔案群組 B (磁碟 2)
  大表水平分區 → 多磁碟控制器並行 I/O
  TEXT/IMAGE/BLOB → 專用檔案群組 + 專用控制器

二、硬體資源規劃

三、索引與統計資料優化

• 叢集索引鍵選擇：窄、唯一、遞增、靜態 (Identity/日期)
• 非叢集索引覆蓋查詢：INCLUDE 欄位避免 Key Lookup
• 填充因子依工作負載設定 (參考 84761)
• 碎片/統計自動化維護 (Ola Hallengren / 維護計畫)
• 避免過度索引：OLTP 表建議 ≤ 5-6 個非叢集索引

四、查詢與預存程序優化

核心原則

1. SET SHOWPLAN_ON / 實際執行計畫 分析：Table Scan、Index Scan、Key Lookup、Sort、Hash Match、Parallelism
2. SARGable 條件寫法：WHERE Col = @val 而非 WHERE FUNCTION(Col) = @val
3. 避免大交易、HOLDLOCK、游標；用集合思維、臨時表/表變數分步處理
4. EXISTS 代替 COUNT(*) 判斷存在性
5. 參數嗅探對策：OPTION (RECOMPILE)、OPTIMIZE FOR UNKNOWN、區域變數賦值
6. 動態 SQL 安全參數化：sp_executesql + 參數化，重用計畫、防注入

常見反模式與重構

五、監控與持續改善

• DMV 核心視圖 (2005+)：
  sys.dm_exec_query_stats (Top CPU/IO/Duration)
  sys.dm_exec_requests (即時阻塞/等待)
  sys.dm_os_wait_stats (等待類型分析：PAGEIOLATCH、CXPACKET、LCK_M_、WRITELOG)
  sys.dm_db_index_usage_stats (未使用索引偵測)
• 查詢存放區 (Query Store, 2016+)：計畫變更回歸、強制計畫
• 擴充事件 (Extended Events)：輕量級取代 Profiler
• 基準線建立：記錄正常時期關鍵指標，異常時快速比對

結語

SQL Server 效能優化是系統工程：**設計奠基、硬體支撐、索引/統計維護、查詢重構、持續監控** 五大支柱缺一不可。建立「基準線 → 異常偵測 → 根因分析 → 驗證修正 → 知識沉澱」閉環，讓資料庫持續在最佳狀態服務業務。