SQL Server 效能優化經驗彙總：索引、查詢重寫、系統配置與監控實務

核心優化原則

優化本質：在結果正確前提下，撰寫優化器可識別的語句，充分利用索引，減少表掃描 I/O，避免鍵查詢與排序溢寫。

十大注意事項

1. 關鍵欄位建立索引：WHERE/JOIN/GROUP BY/ORDER BY 高頻欄位。考慮叢集索引鍵窄、遞增、靜態。
2. 善用預存程序：參數化、計畫重用、減少網路往返、封裝業務邏輯。
3. 定期備份與清理垃圾資料：縮小資料庫體積、縮短備份/還原/ DBCC 時間窗。
4. SQL 語法優化：避免 SELECT *、隱含轉換、非 SARGable 條件 (函數包裹欄位)、OR 破壞索引查找。
5. 清理交易記錄：簡單復原模式定期 BACKUP LOG ... WITH TRUNCATE_ONLY (舊版) / 完整模式定期記錄備份。
6. 索引設計策略：
   • 大量重複值 + 範圍查詢/排序/分組 → 叢集索引候選。
   • 多欄位常同時存取 + 各有重複值 → 組合索引，前導欄位為最高選擇性/最常用欄位。
   • 覆蓋索引 (INCLUDE) 消除鍵查詢。
7. IS NULL / IS NOT NULL：含 NULL 欄位不進索引，WHERE Col IS NULL 觸發掃描。改用 WHERE Col = -1 (哨兵值) 或過濾索引 WHERE Col IS NOT NULL。
8. EXISTS 優於 IN：EXISTS 為相關子查詢，可短路；IN 產生列表掃描。幾乎所有 IN 子查詢可改寫為 EXISTS。
9. 海量查詢減少格式轉換：CONVERT, CAST, FORMAT 在 WHERE/JOIN 破壞索引。轉換移至 SELECT 清單或應用層。
10. ORDER BY / GROUP BY 受益於索引：索引順序符合排序/分組順序可避免 Sort 運算子。含 NULL 欄位需注意。
11. 欄位運算移至等號右側：WHERE DateCol + 1 > @Date → WHERE DateCol > DATEADD(DAY, -1, @Date)。任何欄位運算導致掃描。
12. IN / OR 拆分：產生工作表、索引失效。若重複值少，拆為多條 UNION ALL + 索引提示。
13. 查看執行計畫：SET SHOWPLAN_ALL ON / SSMS 圖形化計畫 / sys.dm_exec_query_plan。識別 Scan、Key Lookup、Sort、Parallelism 不平衡。
14. DBCC 完整性檢查：定期 DBCC CHECKDB，離峰執行，PHYSICAL_ONLY 加速。
15. 謹慎使用游標：必要時將符合條件列轉入暫存表，再對暫存表定義游標，大幅降低鎖與 Tempdb 壓力。

系統層面優化

• MAXDOP：OLTP 設為 1~4，避免平行化開銷；資料倉庫視核心數調整。
• Cost Threshold for Parallelism：預設 5 過低，建議 30~50。
• Max Server Memory：保留 4~8 GB 給 OS，其餘給 SQL Server。
• Tempdb 多檔案、預設大小、瞬態儲存 (參見 SQL 2016 Tempdb 增強)。
• Instant File Initialization：授予服務帳號 SE_MANAGE_VOLUME_NAME，加速資料檔案擴展。
• Lock Pages in Memory：鎖定緩衝池防止分頁 (需權衡 OS 壓力)。

監控與診斷工具鏈

結語

SQL 效能優化是邏輯設計 → 索引設計 → 查詢重寫 → 系統配置 → 持續監控的系統工程。最大收益來自正確的邏輯/索引/查詢設計；系統參數調校為錦上添花。建立基線、設定告警、定期複盤 Top N 慢查詢，養成「優化即治理」文化。