SQL Server 效能優化 50 條鐵律：索引、鎖、查詢、硬體、架構全覆蓋

慢查詢十大根因

1. 無索引或索引失效 (最常見)
2. I/O 吞吐瓶頸
3. 缺計算欄位導致掃描
4. 記憶體不足
5. 網路延遲
6. 單次回傳資料量過大
7. 鎖/死鎖 (程式設計缺陷)
8. sp_lock/sp_who 顯示讀寫爭用
9. 回傳不必要欄位/列
10. SQL 撰寫不佳、未優化

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

1. 分離 I/O：資料、記錄、索引、Tempdb 放不同磁碟/RAID 組
2. 垂直/水平分表：sp_spaceuse 監控表大小，縮小掃描範圍
3. 升級硬體：SSD、更多核心、更大記憶體
4. 依查詢條件建索引：填充因子預設 0，索引鍵越窄越好，低基數欄位 (性別) 勿建單列索引
5. 提升網路頻寬/降低延遲
6. 擴充記憶體：32-bit 啟用 AWE，64-bit 直接受益；虛擬記憶體 = 1.5x 物理 (含全文檢索 3x)；max server memory 設 1.5x 物理
7. 增加 CPU 核心：並行查詢自動評估，適用複雜掃描/排序/連接/GROUP BY；DML 不並行
8. LIKE 優化：LIKE 'a%' 走索引，LIKE '%a' 掃描；長字串用 VARCHAR + 全文索引
9. DB Server / App Server 分離；OLTP / OLAP 分離
10. 分散式分區檢視表：水平分區 → 成員表 → 同名分散式檢視表 → 應用透明存取

維護與設定優化 (11-20)

11. 重建/整理索引：DBCC REINDEX、DBCC INDEXDEFRAG；收縮 DBCC SHRINKDB/FILE；大庫關閉自動增長
12. 事務處理：動態 SQL 外層包 BEGIN TRAN ... COMMIT，勿內嵌
13. WHERE 限制回傳行：避免全表掃描鎖表、浪費 I/O 頻寬
14. 註解不影響執行
15. 少用游標：改用 WHILE、臨時表、Table 變數、子查詢、CASE
16. 游標並發選項：READ_ONLY 最輕量、OPTIMISTIC 衝突少、SCROLL_LOCKS 悲觀需在事務中開啟
17. Profiler 追蹤 + 索引調優精靈
18. UNION ALL > UNION (無去重開銷)
19. 慎用 DISTINCT/ORDER BY：客戶端排序/去重
20. 不回傳不需要的欄位/列

查詢改寫技巧 (21-35)

21. SET QUERY_GOVERNOR_COST_LIMIT / SET ROWCOUNT 限制資源/行數
22. 避開不走索引的運算子：IS NULL、<>、!=、NOT、NOT IN、NOT EXISTS、LIKE '%...'、欄位上加函數 → 改用計算欄位+索引
23. Query Analyzer 看執行計畫：20% 代碼消耗 80% 資源
24. 強制指定索引：FROM Table WITH (INDEX=IdxName)
25. 預計算結果表：複雜報表離線物化
26. MIN/MAX 可走索引
27. 邏輯下推：Default → Rule → Trigger → Constraint → Procedure (越靠近資料越快)
28. 大二進位寫入：Stored Procedure 避免雙向 Base64 轉換
29. BETWEEN 有時快於 IN (範圍掃描 vs 多點查找)
30. 臨時表索引：視情況建立，權衡建立成本
31. 報表類無需事務
32. OR 拆分為多查詢 UNION ALL，嘗試匹配索引
33. 少用檢視表：嵌套檢視表查詢路徑長；改用 Stored Procedure；檢視表索引 (Indexed View) 可加速
34. DISTINCT/ORDER BY 下推客戶端
35. IN 清單把高頻值放前面

進階陷阱與最佳實踐 (36-50)

36. SELECT INTO 鎖系統表 → 禁止在事務中使用；改 CREATE TABLE + INSERT
37. GROUP BY/HAVING 前在 WHERE 過濾；單純分組用 DISTINCT 更快
38. 批次更新優於逐筆更新
39. Table 變數 > 臨時表 (較輕量)
40. 計算欄位可建索引 (2000+)：確定性、非 LOB、ANSI 選項 ON
41. 業務邏輯下沉 Stored Procedure：預編譯、執行計畫快取、減少網路往返
42. 避免重複呼叫函數 → 變數暫存結果
43. EXISTS > COUNT(*) (存在性判斷)
44. 執行緒池設定：記憶體足 → max worker threads = max connections + 5；否則啟用執行緒池
45. 固定鎖定順序：所有 SP 同序鎖表 A→B，防死鎖
46. Performance Monitor 關鍵計數器：Memory: Pages/sec、Processor: % Time、PhysicalDisk: Queue Length、SQLServer: Cache Hit Ratio
47. 隱含轉換殺效能：WHERE FloatCol = 3000 → 應寫 3000.0
48. JOIN 條件順序：A=B, B=@id, A=@id 讓優化器更早過濾
49. 條件分支優化：無參數時無 WHERE，有參數時單一等值比較最快
50. 分頁新寫法：Table 變數 + IDENTITY + TOP，配合索引順序