SQL Server 7.0/2000 時代雖無現代 Query Store、自動調優，但 Profiler + Index Tuning Wizard + Query Analyzer 三劍客構成了經典的「捕獲負載 → 分析建議 → 驗證效果」閉環。本文以完整實戰流程重現這套方法論，並對應現代工具鏈說明演進脈絡。
-- 建立測試表
CREATE TABLE dbo.TestTable (
NKey1 INT IDENTITY(1,1), -- 唯一遞增鍵
Col2 CHAR(300) DEFAULT 'abc', -- 填充列，每行 300 字節
CKey1 CHAR(1) -- 低基數列 (a/b/c/d/e)
);
-- 建立叢集索引於 CKey1 (低基數，適合範圍掃描)
CREATE CLUSTERED INDEX IX_TestTable_CKey1 ON dbo.TestTable(CKey1);
-- 非叢集索引於 NKey1 (高基數，適合點查詢)
CREATE NONCLUSTERED INDEX IX_TestTable_NKey1 ON dbo.TestTable(NKey1);
-- 填充 10,000 筆測試資料 (每值 2,000 行)
DECLARE @Counter INT = 1;
WHILE @Counter <= 2000
BEGIN
INSERT dbo.TestTable (CKey1) VALUES ('a'),('b'),('c'),('d'),('e');
SET @Counter = @Counter + 1;
END;
-- 兩個典型查詢負載
-- Q1: 低基數列範圍查詢 (回傳 20% 資料)
SELECT CKey1, Col2 FROM dbo.TestTable WHERE CKey1 = 'a';
-- Q2: 高基數列點查詢 (回傳 1 行)
SELECT NKey1, Col2 FROM dbo.TestTable WHERE NKey1 = 5000;/* Index Tuning Wizard 輸出 */
USE [TestDB];
BEGIN TRANSACTION;
CREATE CLUSTERED INDEX [IX_TestTable_CKey1] ON [dbo].[TestTable] ([CKey1]);
IF (@@ERROR <> 0) ROLLBACK TRANSACTION;
CREATE NONCLUSTERED INDEX [IX_TestTable_NKey1] ON [dbo].[TestTable] ([NKey1]);
IF (@@ERROR <> 0) ROLLBACK TRANSACTION;
COMMIT TRANSACTION;解讀：精靈正確識別 CKey1 適合叢集索引 (低基數、範圍查詢)、NKey1 適合非叢集索引 (高基數、點查詢)。
-- 建立跟蹤存入表
EXEC sp_trace_create @TraceID OUTPUT, 2, N'', 50, NULL; -- 檔案
-- 或存入表 (需預建表結構)
EXEC sp_trace_create @TraceID OUTPUT, 1, N'dbo.ProfilerLog', 50, NULL; -- 表
-- 分析查詢：Top 3 CPU 消耗查詢
SELECT TOP 3 TextData, CPU, Reads, Writes, Duration
FROM dbo.ProfilerLog
ORDER BY CPU DESC;建議：大量跟蹤資料表上建立 CPU、Duration 非叢集索引加速分析查詢。
| 時代 | 工具 | 核心進化 |
|---|---|---|
| SQL 7.0/2000 | Index Tuning Wizard | 離線分析 .trc，輸出建立腳本 |
| SQL 2005-2016 | DTA (Database Engine Tuning Advisor) | 支援工作負載表、XML 輸入、分區索引、統計資料模擬 |
| SQL 2017+/Azure SQL | 自動調優 | AI 驅動：自動建立/驗證/回滾索引、強制計畫修正 |
SET STATISTICS IO ON;
SELECT CKey1, Col2 FROM dbo.TestTable WHERE CKey1 = 'a';
SELECT NKey1, Col2 FROM dbo.TestTable WHERE NKey1 = 5000;| 查詢 | 建立索引前 | 建立索引後 | 改善分析 |
|---|---|---|---|
| Q1: CKey1='a' | Scan 1, Logical 400, Physical 382, Read-ahead 400 | Scan 1, Logical 91, Physical 5, Read-ahead 32 | 叢集索引定位到 'a' 的資料頁，邏輯讀降 77%，物理讀降 99% |
| Q2: NKey1=5000 | Scan 1, Logical 400, Physical 282, Read-ahead 400 | Scan 1, Logical 5, Physical 0, Read-ahead 0 | 非叢集索引直接定位葉節點，邏輯讀降 99%，零物理讀 |
關鍵指標：Logical Reads (記憶體頁讀取) 是效能調優最穩定指標，不受快取污染影響。
| 模式 | 啟用方式 | 輸出特徵 | 適用場景 |
|---|---|---|---|
| 圖形化 | Ctrl+L (預估) / Ctrl+M+執行 (實際) | 圖示流程圖，滑鼠懸停顯示成本、行數、謂詞 | 快速理解計畫結構、發現掃描、鍵查詢、排序、雜湊匹配 |
| 文字完整 | SET SHOWPLAN_ALL ON | 詳細屬性：EstimatedRows, EstimatedTotalSubtreeCost, Partitioned, Parallel | 深度分析、腳本化比較、文件記錄 |
| 文字精簡 | SET SHOWPLAN_TEXT ON | 僅操作樹結構 | 快速瀏覽、日誌嵌入 |
INCLUDE 欄位覆蓋索引TOP 避免全排序Baseline_YYYYMMDD.trcIndexRecommendations_Baseline.sqlLogical Reads, CPU, Duration 基線值Plan_Baseline_*.sqlplanONLINE = ON (Enterprise 版)EXEC sp_updatestatsRML Utilities (ReadTrace/OSTRESS) 回放 .trc 負載sys.dm_db_index_usage_stats 發現未使用索引、〈sys.dm_db_missing_index_details〉 發現缺失索引| 陷阱 | 症狀 | 根因 | 對策 |
|---|---|---|---|
| 過度索引 | INSERT/UPDATE 變慢、磁碟暴增、鎖爭用 | 盲目套用精靈建議、未審查現有索引 | 嚴格審查、定期清理 user_seeks=user_scans=user_lookups=0 索引 |
| 統計資料過期 | 計畫突變、行估偏差 100 倍 | 大量資料變更後未更新統計 | AUTO_UPDATE_STATISTICS ON + 維護計畫 UPDATE STATISTICS WITH FULLSCAN |
| 參數嗅探 | 同一預存程序參數不同導致計畫災難 | 首次編譯參數值非典型 | OPTION (RECOMPILE) / OPTIMIZE FOR UNKNOWN / 計畫指南 |
| 隱含轉換 | Index Seek 變 Index Scan | WHERE 列與參數型別不符 (VARCHAR vs NVARCHAR) | 統一型別、顯式 CAST、計算欄位索引 |
Profiler、Index Tuning Wizard、Query Analyzer 這三劍客已完成歷史使命，但它們代表的「捕獲真實負載 → 科學分析瓶頸 → 驗證優化成效」方法論永不過時。現代工具鏈 (Query Store、DTA、自動調優、Extended Events、DMV、RML Utilities) 只是將每個環節自動化、視覺化、智能化。掌握核心指標 (Logical Reads、CPU、Duration)、讀懂執行計畫、建立基線與回放驗證閉環，才是 DBA 與開發者的核心競爭力。
