查詢變慢十大常見根因
- 缺索引或索引未命中 (最常見，屬設計缺陷)
- I/O 吞吐瓶頸
- 缺計算欄位導致無法求助索引
- 記憶體不足
- 網路延遲
- 結果集過大 (分頁、分批查詢)
- 鎖/死鎖 (設計缺陷)
sp_lock/sp_who 顯示讀寫爭用- 返回不必要的行/列
- SQL 寫法不佳、未優化
架構層優化 (1-10)
- 檔案分離：資料、日誌、索引置於不同磁碟/RAID 組；
tempdb 放最快存儲 (NVMe/RAID 0)。
- 資料分區：水平分割大表 (範圍/雜湊)，縮減單表體積 (
sp_spaceuse 監控)。
- 硬體升級：CPU 核心、記憶體、存儲 IOPS 按瓶頸補強。
- 索引策略：依查詢條件建立覆蓋索引，填充因子預設 0；避免低選擇性欄位 (如性別) 建單列索引。
- 網路優化：萬兆網卡、啟用 RSS、檢查交換機非阻塞。
- 應用/資料庫分離：OLTP 與 OLAP 物理隔離；考慮分散式分區檢視實現伺服器聯邦。
- 維護計畫：定期
DBCC REINDEX/INDEXDEFRAG、SHRINKFILE；**生產禁用自動增長**，預先配置空間。
- 交易管理：
COMMIT 提交當前事務，ROLLBACK 回滾所有；動態 SQL 外層包事務，勿內嵌。
- WHERE 限制掃描：避免全表掃描鎖表、浪費 I/O 與網路。
- 註釋零開銷：隨意寫，不影響執行計畫。
T-SQL 寫法優化 (11-30)
- 游標替代：優先用集合操作、
WHILE+暫存表、Table Variable、CASE 表達式；游標併發選項 READ_ONLY、OPTIMISTIC、SCROLL_LOCKS 依場景選擇。
- Profiler + 索引調優顧問：捕捉慢查詢、分析計畫、自動推薦索引。
- UNION ALL > UNION：無去重需求時避免排序開銷。
- 慎用 DISTINCT/ORDER BY：能在應用層處理就下推；同理
UNION。
- 僅選需要欄位：
SELECT * 禁用。
- 資源總督：
SET QUERY_GOVERNOR_COST_LIMIT、SET LOCK_TIMEOUT、SELECT TOP n、SET ROWCOUNT 限制資源消耗。
- SARGable 條件：
LIKE 'a%' 走索引，LIKE '%a'/'%a%' 全掃；用 VARCHAR 非 CHAR 存變長文本；IS NULL/NOT IN/<> 等不走索引，改寫為 EXISTS/NOT EXISTS/LEFT JOIN ... IS NULL。
- 查詢分析器看計畫：20% 代碼消耗 80% 資源，聚焦優化熱點。
- 強制索引提示：
FROM tbl WITH (INDEX(idx_name)) 當優化器選錯時介入。
- 預計算物化：報表類聚合結果預存摘要表，查詢秒級返回。
- MIN/MAX 走索引：建立對應索引即可極速取極值。
- 邏輯下推：Default → Rule → Trigger → Constraint → Stored Procedure，越靠近儲存引擎越快。
- 大二進位用預存程序：避免應用層 Base64 編碼雙倍傳輸，
INSERT ... VALUES (@img) 直接寫入。
- BETWEEN > IN：範圍查詢優化器更易用索引求區間。
- 暫存表索引：大臨時表可建索引，但權衡建立成本。
- 避免無謂事務：唯讀報表不開事務。
- OR 改 UNION：
WHERE a=1 OR b=2 → SELECT ... WHERE a=1 UNION ALL SELECT ... WHERE b=2，各分支獨立匹配索引。
- 少用視圖/嵌套視圖：視圖解析開銷大，改用預存程序；必要時建立索引視圖。
- 客戶端排序/去重：
ORDER BY/DISTINCT 下推前端。
- IN 列表順序：高頻值前置，減少比較次數。
- SELECT INTO 鎖系統表：改用
CREATE TABLE #tmp ... INSERT SELECT；**嚴禁在事務中 SELECT INTO**。
- GROUP BY/HAVING 晚剔除：
WHERE 先過濾 → GROUP BY → HAVING 過濾分組；單純分組用 DISTINCT 更快。
- 批次更新：單語句更新多行優於迴圈逐行。
- Table Variable > 暫存表：變數型別無鎖系統表、重編譯少，優先使用。
進階優化 (31-50)
- 計算欄位索引 (2000+)：確定性運算式、非 LOB 型別、
ANSI_NULLS ON 等設定滿足即可建索引。
- 業務邏輯下推：預存程序預編譯、優化、計畫快取，減少網路往返；UDF 返回大結果集改用預存程序。
- 函數結果快取：同一語句重複呼叫函數 → 存變數複用。
- COUNT(*) 替代：
EXISTS 判斷存在性更快；注意 COUNT(nullable_col) 忽略 NULL。
- 執行緒池配置：記憶體足夠時
max worker threads = max connections + 5；否則啟用執行緒池 (< max connections)。
- 統一鎖序：所有預存程序按固定順序鎖表 (A→B)，防死鎖。
- Performance Monitor 關鍵計數器：
- Memory: Pages/sec 持續高 → 記憶體瓶頸。
- Processor: % Processor Time > 95% → CPU 瓶頸。
- PhysicalDisk: Current Queue Length > 1.5×磁碟數 → 磁碟瓶頸。
- SQLServer: Cache Hit Ratio < 80% → 增加記憶體。
- 隱性轉換消除：
WHERE float_col > 3000 → 3000.0，避免執行期 CONVERT。
- 連接條件順序：將等值常數條件前置 (如
b.ref = 'ID' AND a.id = b.ref)，助優化器早期過濾。
- 動態 SQL 參數化：
IF @code IS NULL SELECT * FROM t ELSE SELECT * FROM t WHERE code=@code 比 WHERE code BETWEEN @c1 AND @c2 快 (等值最優)。
- 分頁新寫法 (Table Variable + ROW_NUMBER)：按主鍵順序預取 Top N 再篩選頁碼，配合窄索引極速；若按非索引欄位排序則傳統法更優。
總結
調優無銀彈，唯有「測量 → 定位瓶頸 → 針對性優化 → 驗證」循環。將上述 50 條內化為肌肉記憶，配合持續監控，即可駕馭 SQL Server 高負載場景。
