前言

查詢計畫是優化器的「思維導圖」。本文延續基礎圖形解讀，深入剖析**運算子內部原理、Join 三大演算法細節、統計資訊與基數估計、平行度與記憶體授予**、並給出生產級優化決策清單。

一、核心運算子內部原理

1. Scan 族群：為什麼慢？

• Table Scan：堆積表掃描 IAM 頁 → 資料頁，逐頁讀取，邏輯讀 = 總頁數。
• Clustered Index Scan：掃描叢集索引葉層 (即資料頁)，順序讀取，**同樣是全掃**，只是資料有序。
• Index Scan：掃描非叢集索引葉層，**窄索引頁密度高**，比叢集掃描少 I/O，但仍是全掃。

**優化關鍵**：將 Scan 轉為 Seek。前提：SARGable 條件 + 合適索引 + 統計資訊準確。

2. Seek 族群：精準定位

• Index Seek：B+ 樹從根節點向下導航，僅讀取符合範圍的頁，**邏輯讀 ≈ 樹深 + 符合頁數**。
• Clustered Index Seek：直接定位資料列，**單次 Seek 即取得完整行**，無需 Key Lookup。

3. Key Lookup (書籤查詢) 陷阱

非叢集索引 Seek 後，若 SELECT 清單含非索引欄位，需再按叢集鍵回查叢集索引。**大量 Key Lookup = 隨機 I/O 暴增**。解決：覆蓋索引 (INCLUDE) 或 欄位投影縮減。

4. Sort / Hash Match / Spools 記憶體消費者

• Sort：需記憶體授予，不足溢寫 tempdb。索引有序可消除 Sort。
• Hash Match (Join/Aggregate)：Build 階段建雜湊表佔記憶體，不足溢寫。**統計資訊低估基數是禍首**。
• Table Spool / Index Spool：臨時物化中間結果，通常為優化器應對缺索引/統計錯誤的補救，隱含效能風險。

二、Join 三大策略深度剖析 (補充版)

1. Nested Loops Join

• 外部輸入驅動：外部行數越少越好 (理想 < 1000)。
• 內部輸入需索引 Seek：無索引退化為 Naive Nested Loops (內部全掃)，災難性效能。
• 並行化：外部輸入可平行分發 (Parallelism 操作子)，內部仍串列。

2. Merge Join

• 強制輸入有序：若無索引順序，優化器會插入 Sort，**Sort 成本往往超過 Merge 本身**。
• 多對多處理：需臨時表緩存重複值，**嚴重拖慢**，建議加唯一索引或改 Hash Join。
• 殘留謂詞：合併條件外的額外篩選 (WHERE 其它條件)，在合併後再篩選。

3. Hash Join

• Build Input (小表) → Probe Input (大表)：優化器自動選擇較小者建雜湊表。
• 雜湊桶溢位：記憶體不足時分割寫入 tempdb，**I/O 爆增**。
• Bit Vector Filtering (星型聯結)：維度表建位向量，事實表掃描時快速過濾，大幅減少 Probe 成本。
• 適用聯結型別：Inner, Left/Right/Full Outer, Semi Join, Anti-Semi Join, Union, Intersect, Except, Distinct, Group By。

三、統計資訊與基數估計：計畫品質的生命線

問題	徵兆	解決
統計資訊過舊	實際行數 >> 預估行數，導致選錯 Join 策略、記憶體授予不足	UPDATE STATISTICS、AUTO_UPDATE_STATISTICS ON、PERSIST_SAMPLE_PERCENT
參數嗅探	首次編譯參數值不具代表性，計畫固化後其他參數效能極差	OPTION (RECOMPILE)、OPTIMIZE FOR UNKNOWN、OPTIMIZE FOR (@p = typical)、Plan Guide
多語句 TVF / 表變數	無統計資訊，預估 1 行	改用內嵌 TVF、臨時表 (有統計)、OPTION (RECOMPILE)
相關性欠缺	多欄位條件獨立性假設失敗	多欄位統計資訊、計算欄位+索引、篩選索引

四、生產級優化決策清單

1. 抓取實際計畫：SET STATISTICS IO, TIME ON + 實際執行計畫 (Ctrl+M) 或 Query Store / Extended Events。
2. 定位 Top 3 高成本運算子：Scan、Key Lookup、Sort、Hash Match Spill、Parallelism 不平衡。
3. 索引優化：缺少索引提示 → 測試建立 → 驗證 sys.dm_db_index_usage_stats 讀寫比。
4. 統計資訊維護：大表採樣率、增量統計、檢視 sys.dm_db_stats_properties 修改計數器。
5. 查詢改寫：拆分超大查詢、物化中間結果到臨時表、消除標量 UDF、改寫 OR/IN/NOT IN。
6. 平行度控制：MAXDOP、Cost Threshold for Parallelism、Resource Governor 工作負載群組。
7. 驗證回歸：相同資料量、相同參數、多次執行取中位數，對比邏輯讀、CPU、耗時、等待型別。

結語

查詢計畫不是靜態圖片，而是**優化器在特定統計資訊、參數、資源限制下的動態決策**。深度理解運算子原理、Join 演算法、統計資訊機制，建立「觀察 → 假設 → 驗證 → 固化」的科學優化流程，才能在複雜生產環境中持續交付高效能查詢。