從索引、I/O、鎖、硬體到架構的全鏈路診斷體系
查詢慢從來不是單一原因。本文歸納 11 大核心根因,並給出從參數調優、架構調整到硬體升級的分層解決方案。
11 大核心根因 (按高發頻率排序)
- 缺索引/索引失效:最常見,程式設計缺陷。`EXPLAIN` 看 `table_scan` / `Using filesort`
- 鎖/死鎖:讀寫競爭資源。`sp_lock`、`sp_who2`、擴展事件監控 `deadlock_graph`
- I/O 瓶頸:吞吐不足。`sys.dm_io_virtual_file_stats` 觀察 latency > 20ms
- 記憶體不足:Buffer Pool 容量不夠,頁頻繁換入換出。目標 PLE (Page Life Expectancy) > 300s
- 查詢語句差/無優化:掃描過多行、隱式轉換、參數嗅探、不當提示
- 返回過多行/列:網路傳輸、客戶端記憶體壓力。嚴格投影欄位、分頁
- 統計資訊過期:優化器誤估行數選錯計畫。`UPDATE STATISTICS` / 自動更新閾值調整
- 參數嗅探:首次編譯參數值不具代表性。`OPTION (RECOMPILE)` / `OPTIMIZE FOR UNKNOWN` / 區域變數
- 網路延遲/頻寬:應用與 DB 分離、跨機房。壓縮協議、連線池、就近部署
- CPU 壓力:高編譯/重編譯、複雜運算、缺少並行度配置。`MAXDOP` / `Cost Threshold for Parallelism`
- TempDB 爭用:臨時表、排序、版本存儲溢出。多數據檔案、Trace Flag 1118、優化查詢減少 TempDB 使用
分層優化實戰清單
Level 1: 索引與查詢重寫 (ROI 最高)
- 依 `WHERE`/`JOIN`/`ORDER BY` 建立**覆蓋索引** (含 `INCLUDE` 欄位)
- 消除 `SELECT *`、隱式轉換、欄位上函數 (`WHERE YEAR(dt)=2023` → `WHERE dt >= '2023-01-01' AND dt < '2024-01-01'`)
- 大表分頁:`WHERE id > @last_id ORDER BY id LIMIT 20` (游標分頁) 替代 `OFFSET`
- `LIKE '%kw%'` → 全文索引 / Elasticsearch / `CHARINDEX` + 計算欄索引
Level 2: 實例與資料庫配置
- `MAXDOP` = 核心數/2 (上限 8);`Cost Threshold for Parallelism` = 50
- `fill factor` 80~90 (高寫入表);定期 `REBUILD`/`REORGANIZE` 索引
- 啟用 **查詢存儲** (Query Store) 追蹤計畫退化
- `tempdb` 多數據檔案 (每核 1 檔,上限 8),啟用 TF 1118
Level 3: 存儲與硬體
- **資料/日誌/TempDB/備份** 分離到不同物理磁碟/RAID 組
- SSD/NVMe 存放資料與日誌;RAID 10 (寫入密集) / RAID 5 (讀多寫少)
- 記憶體 ≥ 僅資料集熱點大小;啟用 **Lock Pages in Memory** (需企業版)
Level 4: 架構層面
- **讀寫分離**:主庫寫、從庫讀 (AlwaysOn AG / 複寫 / 中間件)
- **分庫分表**:水平拆分 (Sharding) / 垂直拆分
- **OLTP/OLAP 分離**:核心交易庫 + 同步至數據倉庫 (ClickHouse/Synapse)
- **分散式分區視圖** (DPV) / **PolyBase** 跨實例查詢
維護自動化
-- 索引維護作業 (Ola Hallengren 腳本推薦)
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y';
總結
查詢優化是**持續迭代**過程:**監控基線 → 定位 Top N 慢查詢 → 根因分析 → 索引/重寫/配置/架構干預 → 驗證固化**。工具鏈:`sp_BlitzCache`、`Query Store`、`Extended Events`、`PerfMon`/`Grafana`。