SQL Server 查詢效能優化是系統工程,涉及索引、I/O、記憶體、鎖、網路、計畫品質等多個維度。本文將最常見的 13 大致因與對策系統化整理。
| # | 常見致因 | 核心對策 | 驗證指標 |
|---|---|---|---|
| 1 | 缺失索引或索引未被使用 | 依查詢負載建立覆蓋索引;檢查 sys.dm_db_missing_index_details | Execution Plan: Table Scan / Key Lookup |
| 2 | I/O 吞吐瓶頸 | 資料/日誌/Tempdb 分離不同磁碟陣列;RAID 10/SSD;啟用瞬間檔案初始化 | PAGEIOLATCH_* 等待、Avg. Disk Sec/Read > 10ms |
| 3 | 缺乏計算欄/持久化計算欄 | 將常用運算式 (如 Price*Qty) 定義為 PERSISTED 計算欄並建索引 | Scalar Operator 成本高、CPU 壓力大 |
| 4 | 記憶體不足 | 設定 max server memory;增加物理 RAM;檢查 Target vs Total Server Memory | PAGE_LATCH、Memory Grants Pending、PLE < 300s |
| 5 | 網路延遲/頻寬 | 應用與資料庫同機房/同 VPC;啟用連接池;減少往返 (批次、TVP) | ASYNC_NETWORK_IO 等待、Client 端耗時 >> Server 端 |
| 6 | 結果集過大 | 分頁 (OFFSET/FETCH)、增量同步、資料歸檔、只選需要欄位 | Rows Returned >> Rows Needed、網路流量異常 |
| 7 | 鎖/死鎖 | 縮短交易、固定存取順序、ROWLOCK/UPDLOCK、啟用 RCSI | LCK_M_* 等待、Deadlock Graph (Trace 1222/Extended Events) |
| 8 | 活躍會話阻塞鏈 | sp_who2/sp_lock/DMV 定位 Head Blocker;KILL 或優化其查詢 | Blocking Session ID 鏈、等待時間累積 |
| 9 | 回傳不必要行/列 | SELECT * → 明確列清單;WHERE 過濾下推;EXISTS 替代 COUNT | 網路流量、Client 記憶體、Plan Output List |
| 10 | 查詢寫法不佳/計畫劣化 | 參數化、避免函數包裹索引欄、UPDATE STATISTICS、強制計畫 (Plan Guide/Query Store) | Plan Cache 重編譯率、Parameter Sniffing 症狀 |
| 11 | LIKE 模糊查詢無法用索引 | LIKE 'abc%' 可用索引;LIKE '%abc' 需全文索引/反向索引/計算欄 | 全文索引、計算欄 REVERSE(Col) + 索引 |
| 12 | OLTP/OLAP 混跑 | 讀寫分離 (AlwaysOn Secondary/複寫/ETL 到數據倉庫) | 長時間查詢阻塞 OLTP、Tempdb 爭用 |
| 13 | 分散式分區檢視表 | 水平分區表 + 分散式分區檢視表 實現伺服器聯邦 (Scale-Out) | 單機寫入瓶頸、地理分散 |
FILLFACTOR = 80-90 預留空間減少頁分裂。FILLFACTOR = 100 最大密度。ALTER INDEX ... REBUILD WITH (FILLFACTOR = ...) 或維護計畫重組。-- 維護計畫:每日 UPDATE STATISTICS WITH FULLSCAN 核心表 -- 或啟用 AUTO_UPDATE_STATISTICS_ASYNC ON (非同步更新,避免查詢等待)
WHERE VarcharCol = 123 → 索引失效,改 WHERE VarcharCol = '123'。sp_executesql @sql, N'@id INT', @id 重用計畫。COMMIT 提交當前交易;ROLLBACK 回滾**整個巢狀交易** (含外層)。動態 SQL 交易包裹在外層:BEGIN TRAN; EXEC(@sql); COMMIT;。