SQL Server 效能優化 50 條金律：從索引設計到架構調優完整指南

SQL Server 查詢緩慢的成因複雜，本文彙整 50 條實戰優化法則，涵蓋索引、I/O、記憶體、網路、鎖死鎖、T-SQL 寫法、硬體架構等全方位面向，助你系統化排查與解決效能瓶頸。

一、查詢緩慢十大常見成因

1. 缺少索引或索引未被使用 (程式設計缺失)
2. I/O 吞吐量不足形成瓶頸
3. 缺乏計算欄位導致查詢無法優化
4. 記憶體不足
5. 網路速度慢
6. 單次查詢回傳資料量過大
7. 鎖爭用或死鎖 (程式設計缺失)
8. sp_lock/sp_who 顯示活躍使用者讀寫競爭
9. 回傳不必要的行與列
10. 查詢語句未優化

二、架構與硬體層面優化 (1-10)

1. 分離 I/O：資料、記錄、索引置於不同磁碟/RAID 群組；Tempdb 放高速儲存
2. 縱向/橫向分表：sp_spaceuse 監控表大小，垂直拆分熱冷欄位、水平分區歷史資料
3. 硬體升級：CPU、記憶體、SSD/NVMe、10GbE 網路
4. 精準建索引：依查詢條件建立覆蓋索引，填充因子預設 0，優先小位元組欄位，避免低選擇性欄位 (如性別) 單獨建索引
5. 記憶體配置：虛擬記憶體 = 物理記憶體 1.5 倍；啟用全文檢索則 3 倍；max server memory 設為虛擬記憶體一半
6. CPU 並行度：複雜查詢 (排序、連接、群組) 適合並行；DML 不支援並行；依負載自動決定 DOP
7. LIKE 查詢優化：LIKE 'a%' 可用索引；LIKE '%a'/'%a%' 掃描全表；長字串改用 VARCHAR 或全文檢索
8. 架構分離：DB Server / App Server 分離；OLTP / OLAP 分離
9. 分散式分區檢視：水平分區表 + 成員伺服器檢視，實現資料庫聯盟擴展
10. 維護作業：定期 DBCC REINDEX/INDEXDEFRAG、SHRINKDB/FILE、關閉自動增長 (大型 DB)

三、T-SQL 撰寫優化精要 (11-30)

11. 交易控制：動態 SQL 外層包交易 BEGIN TRAN EXEC(@sql) COMMIT，避免動態 SQL 內部寫交易
12. WHERE 限制回傳行數：避免全表掃描鎖表阻塞其他連線
13. 註解不影響執行：但請保持程式碼整潔
14. 避免游標：改用集合操作、臨時表、Table 變數、子查詢、CASE 運算式；若必須用，選 READ_ONLY / FAST_FORWARD 游標
15. Profiler / XEvents 追蹤：找出高耗時語句，配合索引調優精靈
16. UNION ALL 優於 UNION：無去重開銷
17. 慎用 DISTINCT：同 UNION，無必要勿用，可在應用層去重
18. 只 SELECT 需要的欄位：減少 I/O 與網路傳輸
19. 查詢成本限制：SET QUERY_GOVERNOR_COST_LIMIT / sp_configure 'query governor cost limit' 攔截高成本查詢
20. 限制回傳行數：SELECT TOP 100 / SET ROWCOUNT
21. 避免 WHERE 列上加函數：WHERE SUBSTRING(col,1,1)='m' → WHERE col LIKE 'm%'；必要時建計算欄位+索引
22. NOT IN 改用 NOT EXISTS / LEFT JOIN：NOT IN 多次掃描；EXISTS 比 IN 快；NOT 運算最慢
23. 查看執行計劃：專注優化前 20% 佔 80% 資源的慢查詢
24. 強制指定索引：FROM Table WITH (INDEX=IdxName) 當優化器選錯時
25. 預先計算結果表：複雜報表預聚合存儲，查詢時直接讀取
26. MIN/MAX 善用索引：配合索引極值查詢極快
27. 邏輯下推優先序：Default → Rule → Trigger → Constraint → Procedure，越靠近資料越高效
28. 大二進位用 SP 寫入：避免應用層雙向轉換 (Base64↔Binary) 浪費頻寬 CPU
29. BETWEEN 優於 IN：範圍查詢更易用索引
30. 臨時表適度建索引：權衡建索引開銷與查詢收益
31. 無謂交易：報表查詢勿加交易
32. OR 條件拆分 UNION ALL：各分支嘗試匹配不同索引
33. 少用檢視表：巢狀檢視更差；改用 SP；必要時建檢視索引
34. ORDER BY/DISTINCT 下推應用層：減少伺服器排序開銷
35. IN 列表頻繁值前置：減少判斷次數

四、進階陷阱與最佳實務 (31-50)

31. SELECT INTO 鎖系統表：改用 CREATE TABLE #tmp ... INSERT SELECT，切勿在交易中用 SELECT INTO
32. GROUP BY/HAVING 晚濾：WHERE 先濾 → GROUP BY 分組 → HAVING 濾組；純分組用 DISTINCT 更快
33. 批次更新優於逐筆：單陳述式 CASE WHEN 或 MERGE 更新萬筆資料
34. Table 變數替代臨時表：輕量、無鎖系統表、自動清理
35. 計算欄位可索引 (2005+)：確定性運算式、非 LOB 型別、特定 SET 選項 ON
36. 業務邏輯下沉 SP：編譯優化、減少網路往返；複雜數學用 UDF (小結果集) 或 SP (大結果集)
37. 避免重複呼叫函數：結果存變數複用
38. COUNT(*) 優化：EXISTS 比 COUNT(*) 快；注意 COUNT(可空欄位) vs COUNT(非空欄位) 差異
39. 執行緒配置：記憶體足時 max worker threads = max connections + 5；否則啟用執行緒池
40. 固定存取順序防死鎖：所有 SP 依相同順序鎖表
41. Performance Monitor 關鍵計數器：
    • Memory: Pages/sec 持續高 → 記憶體瓶頸
    • Processor: % Processor Time > 95% → CPU 瓶頸
    • PhysicalDisk: Current Disk Queue Length > 1.5×磁碟數 → I/O 瓶頸
    • SQLServer: Cache Hit Ratio < 80% → 需加記憶體
42. 常數型別匹配：WHERE FloatCol = 3000.0 避免隱含轉換
43. JOIN 條件順序影響計劃：舊版優化器敏感，新版較智能，建議仍寫完整等價條件
44. 動態 SQL 參數化：sp_executesql 重用計劃，防注入
45. 分頁新寫法 (Table 變數 + IDENTITY)：按主鍵順序預取 ID 再關聯，避免大 OFFSET 掃描；比較 ORDER BY PK vs ORDER BY UpdateDate 的索引利用差異

總結：優化系統化流程

1. 識別症狀 (慢查詢、高 CPU、I/O 等待、鎖等待)
2. 收集證據 (執行計劃、等待統計、DMV、PerfMon)
3. 定位根因 (缺索引、壞計劃、參數嗅探、鎖升級、硬體極限)
4. 施策驗證 (單一變因、對照測試、監控回歸)
5. 建立基線與告警，持續迭代