SQL Server 查詢緩慢十大成因與優化實戰清單

編輯：關於SqlServer

核心成因速查表

#	成因類別	典型徵兆	優先處理建議
1	缺索引/索引失效	全表掃描、Key Lookup 過多	依查詢條件建立覆蓋索引，檢查 SARGable 條件
2	I/O 瓶頸	PAGEIOLATCH_* 等待高、磁碟佇列長度大	資料/記錄/索引分離不同磁碟，考慮 SSD/RAID
3	缺計算欄位導致掃描	WHERE 子句含函數（SUBSTRING, CONVERT）	建立計算欄位+索引，或改寫為 SARGable 形式
4	記憶體不足	Buffer Cache Hit Ratio 低、Plan Cache 壓力大	擴充實體記憶體，設定 Max Server Memory
5	網路延遲	ASYNC_NETWORK_IO 等待、大量資料傳輸	分頁/篩選縮減回傳資料量，啟用壓縮
6	回傳資料過大	單次查詢回傳數萬列	分批查詢、僅選必要欄位、避免 SELECT *
7	鎖爭用/死鎖	LCK_M_* 等待、死鎖圖頻繁	縮短交易範圍、調整隔離等級、優化索引減少掃描
8	鎖監控盲區	無法快速定位阻塞鏈	善用 `sp_who2`, `sp_lock`, DMV `sys.dm_tran_locks`
9	多餘欄位/列	SELECT *、未過濾不必要行	明確列出欄位，WHERE 子句下推
10	查詢寫法不佳	巢狀子查詢、隱性轉換、OR/IN 濫用	改寫為 JOIN、APPLY、CTV、參數化

WHERE 子句下推：盡早過濾，避免大結果集產生後再篩選。
避免光標：改用集合操作、Table Variable、CTE、CASE 表達式。
UNION ALL > UNION：除非需要去重，否則永遠用 ALL 避免排序開銷。
慎用 DISTINCT：同 UNION，會觸發排序/雜湊去重。
LIKE 模糊查詢優化：
- LIKE 'abc%' → 可用索引
- LIKE '%abc' / LIKE '%abc%' → 全掃描，改用全文索引或前綴搜尋架構
- 長字串欄位用 VARCHAR 而非 CHAR，減少掃描位元組
隔離等級選擇：報表查詢可用 READ UNCOMMITTED (NOLOCK) 降低阻塞，但需接受髒讀風險。
限制資源消耗：SET QUERY_GOVERNOR_COST_LIMIT、SET ROWCOUNT、TOP N 防止失控查詢。
NOT IN / NOT EXISTS 陷阱：含 NULL 時語意不同，優先用 NOT EXISTS 或 LEFT JOIN ... WHERE IS NULL。
函數與索引：WHERE SUBSTRING(col,1,1)='m' → 改寫 WHERE col LIKE 'm%'；必須用函數時建立計算欄位+索引。
定期維護：DBCC REINDEX, INDEXDEFRAG, SHRINKFILE（慎用自動收縮）。

SQL Profiler / Extended Events：捕捉高耗時查詢、參數嗅探問題。
Database Engine Tuning Advisor (DTA)：自動推薦索引/統計資訊，但需人工審核。
DMV 核心查詢：
- sys.dm_exec_query_stats：累積耗時/IO/CPU 排名
- sys.dm_exec_requests：即時阻塞/等待分析
- sys.dm_db_index_usage_stats：索引使用率/維護成本評估