核心指令組合
-- 開啟統計
SET STATISTICS TIME ON; -- 解析/編譯/執行 CPU 時間、經過時間
SET STATISTICS IO ON; -- 邏輯讀/物理讀/預讀/LOB 讀 (最關鍵指標)
SET STATISTICS PROFILE ON; -- 執行計畫文字版 (含估算/實際行數、運算子、成本) -- 注意：2016+ 建議用 SET STATISTICS XML ON 或查詢存儲
GO
-- 你的測試查詢
SELECT * FROM dbo.BigTable WHERE Col1 = 'Filter' ORDER BY Col2;
GO
-- 關閉統計 (養成習慣，避免影響後續批次)
SET STATISTICS PROFILE OFF;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
輸出解讀指南 (重點看「訊息」頁籤)
1. STATISTICS TIME - CPU 與等待分析
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms. -- 解析/編譯階段
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 120 ms. -- 執行階段
- CPU time：實際消耗 CPU 周期。高 CPU** 通常意味著大量計算 (排序、雜湊、聚合、隱含轉換) 或計畫劣化。
- Elapsed time：牆鐘時間。若 **Elapsed >> CPU**，表明大量**等待** (I/O 等待、鎖阻塞、網路傳輸、平行協調)。
- Parse/Compile 時間高** → 計畫未重用 (動態 SQL 拼接、參數嗅探導致重編譯、統計更新觸發重編譯)。
2. STATISTICS IO - I/O 成本核心指標 (調優首看)
Table 'BigTable'. Scan count 1, logical reads 5000, physical reads 0, read-ahead reads 0, lob logical reads 0...
- Logical Reads (邏輯讀)**：從緩存 (Buffer Pool) 讀取 8KB 頁面次數。**調優核心目標 = 最小化 Logical Reads**。直接反映查詢存取路徑效率 (Seek vs Scan、覆蓋索引、聯接策略)。
- Physical Reads (物理讀)**：從磁碟讀取。冷緩存時出現；熱緩存應為 0。持續高物理讀 → 記憶體壓力 / 緩存命中率低。
- Read-Ahead Reads (預讀)**：預測性大量讀取 (64KB/128KB/256KB)。全表掃描/大範圍掃描時出現。
- LOB Logical Reads**：讀取 `MAX`/`XML`/`TEXT` 頁面。大物件存取開銷極大，應盡量避免 `SELECT *` 拉取 LOB 欄位。
- Scan Count**：掃描/搜尋啟動次數。Nested Loops 內層表 Scan Count = 外層行數；Hash/ Merge Join 通常為 1。
3. STATISTICS PROFILE / XML - 計畫與基數估算驗證
- 文字版 (`PROFILE`) 適合快速瀏覽；XML 版 (`XML`) 適合工具分析 (SSMS 圖形化、Plan Explorer、Query Store)。
- 重點檢查：
- Actual Rows vs Estimated Rows** 差距 > 10x → 統計過時/參數嗅探/複雜運算式導致基數估算錯誤 → 計畫劣化根因。
- 運算子類型** : `Index Seek` (好) vs `Index Scan`/`Table Scan`/`Clustered Index Scan` (壞，除非刻意)；`Key Lookup` (RID/Clustered) → 考慮覆蓋索引；`Sort`/`Hash Match`/`Spool` 高成本 → 檢查記憶體授予/索引順序。
- Parallelism** : `Parallelism (Gather/Repartition/Exchange Streams)` → 檢查 `MAXDOP`/`CTFP` 設定是否合理。
- Warnings** : `SpillToTempDb` (記憶體不足溢寫)、`Missing Index` (綠色提示)、`Implicit Conversion` (黃色警告)。
進階：Extended Events (XEvents) 取代 Profiler/Trace
`STATISTICS` 只能測單條查詢。生產環境持續監控需用 XEvents (輕量、可過濾、可持久化)。
-- 捕獲高耗時查詢 (超過 1 秒)
CREATE EVENT SESSION [LongRunningQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.sql_text, sqlserver.database_name, sqlserver.username, sqlserver.client_hostname)
WHERE ([duration] > 1000000) -- 微秒
)
ADD TARGET package0.event_file(SET filename=N'D:\XE\LongRunningQueries.xel', max_file_size=100, max_rollover_files=10)
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS);
GO
ALTER EVENT SESSION [LongRunningQueries] ON SERVER STATE = START;
實戰調優流程 (單查詢)
- `SET STATISTICS IO, TIME ON;` 執行查詢 → 記錄 **Logical Reads、CPU、Elapsed**。
- 查看 **Actual Execution Plan** (Ctrl+M) → 找 **最貴運算子** (Cost %最高) → 檢查 **Estimated vs Actual Rows**。
- 若 **Scan/Seek 不符預期** → 檢查 `WHERE`/`JOIN` 列索引覆蓋、SARGable、統計資訊 (`DBCC SHOW_STATISTICS`)。
- 若 **Sort/Hash Spill** → 檢查記憶體授予 (`Granted Memory` vs `Used Memory`)、`MAXDOP`、索引順序消除 Sort。
- 優化 (加索引/改寫/更新統計/Hint/Plan Guide) → 重測 → 對比 **Logical Reads / CPU / Elapsed** 下降幅度。
總結
「TIME 看 CPU/等待，IO 看邏輯讀 (核心)，PROFILE/XML 看計畫與基數。」
養成「先開統計、跑查詢、關統計、看訊息、對計畫」的肌肉記憶，是 DBA 與開發者調優的基本功。
