SQL 調優的核心在於「看得見」資源消耗。本文深度解析 **`SET STATISTICS IO/TIME`**、**`SET SHOWPLAN_ALL/TEXT/XML`**、**擴充事件** 三大原生分析工具，配合實戰範例示範如何解讀輸出、定位瓶頸、驗證優化成果。

方法一：STATISTICS IO / TIME — 量化資源消耗

SET STATISTICS IO ON;   -- 邏輯/物理/前讀/LOB 讀寫頁數
SET STATISTICS TIME ON; -- CPU/經過時間 (毫秒)
GO

-- 你的查詢
SELECT * FROM dbo.BigTable WHERE Status = 1;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

輸出關鍵欄位解讀

欄位	含義	優化導向
logical reads	從緩衝集區讀取的 8KB 頁數 (含重複)	**核心指標**；降低 = 少讀頁 = 少 CPU/記憶體/鎖。目標：接近「必要頁數」。
physical reads	從磁碟實際讀取的頁數	冷快取指標；熱系統應接近 0。
read-ahead reads	預讀頁數	大範圍掃描特徵；若過大可能索引缺失。
lob logical reads	LOB 資料 (MAX/TEXT/IMAGE) 邏輯讀取	檢查是否必要；考慮 `FILESTREAM`/資料行存儲/外部化。
CPU time / Elapsed time	CPU 耗用 / 執行緒等待總時間	CPU 高 → 計算密集 (標量函數、隱含轉換、複雜運算)；Elapsed ≫ CPU → 等待 (鎖、I/O、鎖閘、網路)。

實戰：同一查詢三種寫法的 IO 對比

-- 1. 全表掃描
SELECT * FROM dbo.SalesOrder;
-- logical reads: 125,000  (全表頁數)

-- 2. TOP 100 無 ORDER BY
SELECT TOP 100 * FROM dbo.SalesOrder;
-- logical reads: 45  (只讀前幾頁)

-- 3. TOP 100 WITH ORDER BY (需排序)
SELECT TOP 100 * FROM dbo.SalesOrder ORDER BY OrderDate;
-- logical reads: 125,000 + Sort (tempdb spill)  -- 必須全掃描排序

-- 4. TOP 100 WITH INDEX SEEK (最佳)
SELECT TOP 100 * FROM dbo.SalesOrder WHERE CustomerID = 123 ORDER BY OrderDate;
-- logical reads: 12 (Index Seek + Key Lookup x 100)

方法二：SHOWPLAN — 靜態執行計畫分析

-- 文字版 (舊版相容)
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM dbo.SalesOrder WHERE CustomerID = 123;
GO
SET SHOWPLAN_ALL OFF;

-- 圖形化 XML 版 (現代標準，可另存 .sqlplan 用 SSMS 開啟)
SET SHOWPLAN_XML ON;
GO
SELECT * FROM dbo.SalesOrder WHERE CustomerID = 123;
GO
SET SHOWPLAN_XML OFF;

計畫 XML 關鍵屬性速查

• StatementOptmLevel：`FULL` (完整優化) / `TRIVIAL` (微優化) / `OPTIMIZED` (受限優化)。
• StatementOptmEarlyAbortReason：`GoodEnoughPlanFound`、`TimeOut`、`MemoryLimitExceeded` — 揭示優化器為何停止搜尋更好計畫。
• MissingIndexes：優化器建議的遺漏索引 (含改善估計 %)。
• Warnings：`SpillToTempDb`、迴圈聯結預估偏差、隱含轉換、統計資料過期。
• ParameterList / `ParameterCompiledValue` / `ParameterRuntimeValue`：**參數嗅探** 現場鑑證。

方法三：擴充事件 — 生產級零侵入持續監控

取代 Profiler/Trace，極低負擔 (約 1-3%)，支援環狀緩衝區、檔案目標、即時觀察。

建立監控 Session：捕捉高耗用查詢

CREATE EVENT SESSION [HighCostQueries] ON SERVER
ADD EVENT sqlserver.sql_batch_completed (
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_name, sqlserver.username)
    WHERE ([duration] > 5000000)  -- 5 秒以上 (微秒)
),
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_name, sqlserver.username)
    WHERE ([duration] > 5000000)
)
ADD TARGET package0.event_file (SET filename = N'C:\XE\HighCostQueries.xel', max_file_size = 100, max_rollover_files = 10)
WITH (MAX_MEMORY = 50 MB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS);
GO

ALTER EVENT SESSION [HighCostQueries] ON SERVER STATE = START;

查詢 XE 檔案分析 Top 慢查詢

SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS EventName,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS Timestamp,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000.0 AS Duration_ms,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SqlText,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'sysname') AS DatabaseName,
    event_data.value('(event/action[@name="username"]/value)[1]', 'sysname') AS LoginName
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\XE\HighCostQueries*.xel', NULL, NULL, NULL)
) x
ORDER BY Duration_ms DESC;

綜合調優流程圖

1. 發現慢 (使用者回報 / XE 告警 / DMV Top CPU/Reads)
    ↓
2. SET STATISTICS IO, TIME ON + 實際執行計畫 (SSMS Ctrl+M)
    ↓
3. 關注：Logical Reads (最高優先) → Warnings (Spill/Implicit Conv/Missing Index) → Wait Stats (若 Elapsed >> CPU)
    ↓
4. 定位根因：
   - 高 Reads → 缺索引 / 掃描而非 Seek / 統計過期 / 參數嗅探
   - Spill → 記憶體授與不足 / 統計偏差 / 需要索引避免 Sort/Hash
   - 隱含轉換 → 資料型別不符 / 函數包裹欄位
   - 並行度問題 → MAXDOP / Cost Threshold for Parallelism
    ↓
5. 實施優化：加索引 / 改寫查詢 / 更新統計 / OPTION (RECOMPILE/OPTIMIZE FOR) / 調整 MAXDOP
    ↓
6. 再次 STATISTICS IO + 實際計畫驗證 → 迴歸測試 → 部署
    ↓
7. 持續 XE 監控確認無回歸

常見誤區提醒

• ❌ 只看「估計執行計畫」 — **必須看實際執行計畫** (含實際列數、實際執行次數、Spill 警告)。
• ❌ 只看 `Duration` — 高 Duration 可能是阻塞/等待，非 CPU 問題；請看 `Wait Stats` 或 `sys.dm_exec_requests`。
• ❌ 盲目加索引 — 寫入負擔、維護成本、計畫不穩定；先驗證 `sys.dm_db_index_usage_stats` 現有索引使用率。
• ❌ 忽略參數嗅探 — 同一計畫參數值差異大時，需 `OPTION (RECOMPILE)` / `OPTIMIZE FOR UNKNOWN` / 動態 SQL 分支。

小結

**STATISTICS IO 量化資源、SHOWPLAN_XML 解剖計畫、XE 持續監控** — 三工具互補，覆蓋從單查詢微調到全系統穩定性的完整鏈路。建議每位 DBA/開發建立個人「調優檢核表」，將上述關鍵指標、警告代碼、常見模式內化為肌肉記憶。