SQL Server 編譯與重編譯深度解析：執行計劃重用機制全解

核心概念

SQL Server 接收任何 T-SQL 指令（查詢、批次、預存程序、觸發器、動態 SQL）後，經歷：

1. 語法解析 → 語義解析 → 編譯 產出執行計劃
2. 依據結構描述、統計資訊、指令內容估算成本，選擇最低成本計劃
3. 計劃緩存於記憶體（Plan Cache），後續相同指令重用避免重複編譯

重用 vs 重編譯：利弊權衡

SQL Server 尋求平衡點，非盲目追求重用或重編譯。

查看快取計劃

-- 注意：生產環境慎用，快取可能龐大
SELECT * FROM sys.syscacheobjects;

觸發重編譯的 5 大場景

1. 結構描述變更

• 資料表/檢視：增刪欄位、索引、約束
• 原計劃失效，必須重編譯

2. 手動觸發

EXEC sp_recompile 'ProcedureName';  -- 下次執行重編譯
EXEC sp_recompile 'TableName';      -- 所有引用該表的 SP 重編譯

3. 快取清空操作

實例級清空：

• Detach 資料庫
• DBCC FREEPROCCACHE
• RECONFIGURE
• ALTER DATABASE ... COLLATE

資料庫級清空：

• DBCC FLUSHPROCINDB(dbid) (SQL 2000)
• ALTER DATABASE ... MODIFY NAME / SET ONLINE / OFFLINE / EMERGENCY
• DROP DATABASE
• 資料庫自動關閉、DBCC CHECKDB 完成

4. SET 選項變更

以下選項改變會導致既有計劃失效：

ANSI_NULL_DFLT_OFF, ANSI_NULL_DFLT_ON, ANSI_NULLS, ANSI_PADDING,
ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, DATEFIRST,
DATEFORMAT, FORCEPLAN, LANGUAGE, NO_BROWSETABLE, NUMERIC_ROUNDABORT,
QUOTED_IDENTIFIER

原因：這些選項影響語義行為與結果正確性。

5. 統計資訊更新

• 手動 UPDATE STATISTICS
• 自動更新觸發（閾值：資料列變更 > 20% + 500）
• 相關查詢將重編譯

實務建議

• 大多數情況下 SQL Server 自動平衡良好，無需過度干預
• 參數嗅探問題可用 OPTION (RECOMPILE)、OPTIMIZE FOR、區域變數緩解
• 穩定效能優於極致優化：避免頻繁 DDL、固定 SET 選項、合理維護統計資訊

總結

理解編譯/重編譯機制有助於診斷效能抖動、計劃失效等疑難雜症。核心原則：重用為主，必要時重編譯，透過穩定的環境設定減少非預期重編譯。