SQL Server 查詢緩慢並非不可治，遵循「硬件→架構→索引→統計→SQL改寫→鎖並行→監控」系統化優化路徑，47 條實戰鐵律覆蓋 95% 性能場景。本文精簡整理，附關鍵 T-SQL 模式與反模式對比。
Target Server Memory 接近 Total Server Memory，Page Life Expectancy > 300s。Lock Pages in Memory 權限授予服務賬號，防內存換頁。MAXDOP 設置為 NUMA 節點核心數 (通常 ≤8)，Cost Threshold for Parallelism = 50 起步，避免小查詢並行開銷。INCLUDE 非鍵列滿足 SELECT 列，消除 Key Lookup (Bookmark Lookup)。WHERE a=1 AND b>2 ORDER BY c → 索引 (a, b, c)。WHERE varchar_col = 123 → 索引失效，改 WHERE varchar_col = '123' 或計算列持久化。AUTO_UPDATE_STATISTICS ON、AUTO_UPDATE_STATISTICS_ASYNC ON (大表)，sp_updatestats 定時維護。WHERE status = 1 高頻查詢 → CREATE INDEX ... WHERE status = 1，極大縮小索引體積。DATA_COMPRESSION = PAGE (OLAP) / ROW (OLTP)，降低 IO、提升緩存命中。REBUILD (ONLINE=ON)；10-30% → REORGANIZE；sys.dm_db_index_physical_stats 監控。| 反模式 (慢) | 優化模式 (快) | 原理 |
|---|---|---|
SELECT * | 顯式列清單 | 減少網絡/IO/緩存壓力，利用覆蓋索引 |
WHERE col IS NULL / IS NOT NULL | 加計算列持久化 + 索引 / 篩選索引 | NULL 不進常規索引 (除過濾索引) |
WHERE col != val / NOT IN / NOT EXISTS | NOT EXISTS 優於 NOT IN (NULL 安全)；LEFT JOIN ... WHERE right.key IS NULL | 反向查找無法用索引 Seek |
WHERE func(col) = val | 計算列持久化 + 索引 / 應用層預計算 | 函數破壞 SARGability |
LIKE '%keyword%' | Full-Text Index / 字首 LIKE 'keyword%' / n-gram 分詞表 | 前導通配符導致掃描 |
OR 連接多列 | UNION ALL 分拆 + 索引合併 / 計算列 | OR 通常導致掃描 |
IN (長列表) | 臨時表/表值參數 JOIN / OPTIMIZE FOR UNKNOWN | 長列表編譯緩存污染、優化器壓力 |
DISTINCT / ORDER BY 濫用 | 業務層去重排序 / 索引順序天然有序 | 額外 Sort 操作 |
| 游標/循環逐行處理 | 集合操作 (JOIN/APPLY/窗口函數) / Table-Valued Parameter | RBAR (Row By Agonizing Row) 極慢 |
| 大事務長鎖定 | 拆分小批次 / READ COMMITTED SNAPSHOT (RCSI) / 延遲提交 | 阻塞鏈、日志膨脹、恢復時間長 |
ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON，讀不阻寫、寫不阻讀，消除 80% 死鎖。Trace Flag 1222 / Extended Events (xml_deadlock_report) / sys.dm_tran_locks 實時查詢。ALTER TABLE SET (LOCK_ESCALATION = AUTO/DISABLE)，分區表用 AUTO。ROWVERSION / TIMESTAMP 列 + WHERE rowversion = @old 更新檢查。Perform Volume Maintenance Tasks，數據文件擴展零填充，秒級擴容。PAGE 壓縮歷史/歸檔表，ROW 壓縮熱表，IO 減少 40-60%。DELAYED_DURABILITY = ALLOWED (非關鍵業務)，日志寫入異步刷盤，吞吐提升 2-3 倍。SORT_IN_TEMPDB = ON (重建索引)、增大 work_mem 等效參數、精簡 SELECT 列。rpc_completed、sql_batch_completed、wait_info。sys.dm_exec_query_stats (Top CPU/IO/時間)、sys.dm_exec_requests (實時阻塞)、sys.dm_os_wait_stats (等待類型分布)。Page Life Expectancy、Buffer Cache Hit Ratio、Lock Waits/sec、SQL Compilations/sec、Batch Requests/sec。/* READ */。Cache-Aside 模式，雙寫一致性方案。建立基線 → 識別 Top 問題 → 定位根因 (等待類型/執行計劃/索引/統計) →
施策 (改寫/索引/架構/參數) → 驗證效果 (對比基線) → 固化最佳實踐 → 持續巡檢**核心信條**：索引是銀彈，統計是瞳孔，改寫是手術刀，架構是防彈衣，監控是雷達。 無監控不優化、無基線不調優、無驗證不上線。
