SQL Server 忽略索引提示的追蹤旗標 8602：效能排查利器

生產環境中，SQL 陳述式若硬編碼索引提示 (WITH (INDEX(...)))，可能導致優化器無法選擇更優計畫。修改程式碼需部署流程，耗時較長。本文介紹追蹤旗標 8602，可在不修改 SQL 的情況下忽略索引提示，快速驗證效能問題。

問題場景

• SQL 陳述式指定 WITH (INDEX(IX_xttrace_bal)) 強制使用特定索引。
• 資料分布變化導致該索引不再最優，但優化器受提示限制無法切換。
• 多支 SQL/預存程序使用索引提示，無法立即修改部署。

解決方案：追蹤旗標 8602

DBCC TRACEON(8602, -1) 啟用後，SQL Server 會忽略所有查詢與預存程序中的索引提示。

完整測試腳本

1. 建立測試環境

USE master;
GO
IF DB_ID('Trace8602') IS NOT NULL DROP DATABASE Trace8602;
GO
CREATE DATABASE Trace8602;
GO
USE Trace8602;
GO

CREATE TABLE xttrace8602
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    bal INT,
    name VARCHAR(100)
);
GO

CREATE NONCLUSTERED INDEX IX_xttrace8602_bal_name ON xttrace8602(bal, name);
GO
CREATE NONCLUSTERED INDEX IX_xttrace8602_bal ON xttrace8602(bal);
GO

INSERT INTO xttrace8602 VALUES (RAND() * 786, 'cnblogs.com/lyhabc');
GO 10000

CREATE PROC uspFirst
AS
    SELECT id, name
    FROM xttrace8602 TF WITH (INDEX (IX_xttrace8602_bal))
    WHERE bal < 100;
GO

2. 未啟用旗標：強制使用 IX_xttrace8602_bal (需鍵值查閱)

EXEC uspFirst;
-- 執行計畫顯示：Index Seek (IX_xttrace8602_bal) + Key Lookup

3. 啟用旗標 8602：忽略提示，改用覆蓋索引 IX_xttrace8602_bal_name

DBCC TRACEON(8602, -1);
GO
DBCC FREEPROCCACHE;  -- 清除計畫快取確保重新編譯
GO
EXEC uspFirst;
-- 執行計畫顯示：Index Seek (IX_xttrace8602_bal_name) 無 Key Lookup，效能更優

關鍵說明

• -1 參數表示全域啟用 (Session 級別用 -1 以外的值或無參數)。
• 需搭配 DBCC FREEPROCCACHE 或 sp_recompile 使現有計畫失效重新編譯。
• 僅影響索引提示 (INDEX, FORCESEEK 等)，不影響其他提示 (如 FORCE ORDER)。

適用場景與風險

最佳實務流程

1. 開發環境啟用 8602 驗證效能差異。
2. 確認索引提示確為負面影響後，規劃移除提示的程式碼變更。
3. 生產環境維護窗口部署移除提示的版本。
4. 部署完成後關閉 8602 (DBCC TRACEOFF(8602, -1))。

追蹤旗標 8602 是 DBA 緊急效能排查的利器，但切記：它是診斷工具，非長期解方。