Recompile 是好還是壞？#

兩者都是。

• 好：資料變動 → 統計更新 → 新計畫可能更適合 → 效能提升
• 壞：頻繁 recompile 浪費 CPU、消耗記憶體、且 recompile 期間其他需要該計畫的查詢會被 block

SQL Server 從 2005 開始 以「敘述（statement）」為單位 重新編譯，不是整個 procedure。整體 recompile 次數可能更高，但每次代價更小、影響範圍更小。

Plan Forcing 與 Recompile 的互動#

Query Store 啟用 plan forcing 時：

• recompile 仍會發生
• 但 重新編譯出來的計畫不會被使用——以 forced plan 取代
• 若 forced plan 已被標為無效（結構變動等），才會用 recompile 後的新計畫
• 若同時 force 了 query hint，hint 會在 recompile 時套用

識別「哪一條敘述」被 recompile#

在大型 procedure 中，光靠 sql_statement_recompile 一個事件常常看不出究竟是哪句。Fritchey 的做法是用 Extended Events session 同時觀察整個生命週期：

關鍵事件：

• sql_batch_starting / sql_batch_completed
• sql_statement_starting / sql_statement_completed
• sp_statement_starting / sp_statement_completed
• sql_statement_recompile
• rpc_starting / rpc_completed

並開啟 Causality Tracking（TRACK_CAUSALITY = ON），事件會被附上 attach_activity_id_guid，可在繁忙系統中把同一個批次的事件抓在一起重組順序。

一個 recompile 的典型事件序列：

1. sql_batch_starting
2. sql_statement_starting
3. sp_statement_starting
4. sql_statement_recompile（被中斷）
5. sp_statement_starting（重啟）
6. sp_statement_completed
7. sql_statement_completed
8. sql_batch_completed

Recompile 的常見原因#

統計可從 sys.dm_xe_map_values 取得完整列表（隨版本增加）：

SELECT dxmv.map_value
FROM sys.dm_xe_map_values AS dxmv
WHERE dxmv.name = 'statement_recompile_cause';

最常見的：

• Schema Changes：表 / 暫存表 / view 的結構、metadata、索引變動
• Binding Changes：欄位 default 等繫結變動
• Statistics Updates：自動或手動觸發
• Deferred Object Resolution：批次內動態建立的物件，第一次執行時延遲解析
• SET Options：執行期間 SET 選項變化
• sp_recompile：明確呼叫
• RECOMPILE hint：查詢 / procedure 上的提示
• Parameter-Sensitive Plans (PSP)：第 13 章詳述
• Optional Parameter Optimization (OPO)：同上

延遲物件解析（Deferred Object Resolution）#

批次中動態建立物件（CREATE TABLE 等）後再使用，會觸發延遲編譯，行為依物件種類而異。

一般 Table#

CREATE OR ALTER PROC dbo.RecompileTable
AS
CREATE TABLE dbo.ProcTest1 (C1 INT);
SELECT * FROM dbo.ProcTest1;
DROP TABLE dbo.ProcTest1;

第一次執行：因 SELECT 引用尚未存在的表 → deferred compile。

第二次執行：DROP 並沒有把 plan 從 cache 移除，但 CREATE TABLE 是 schema 變更 → 再 recompile 一次。

局部暫存表（#TempTable）#

CREATE OR ALTER PROC dbo.RecompileProc
AS
CREATE TABLE #TempTable (C1 INT);
INSERT INTO #TempTable (C1) VALUES (42);

也會 deferred compile，但 暫存表的建立不算 schema 變更——所以不會像一般表那樣被反覆 recompile。

在 procedure 中需要中介結果時，用 #TempTable 比動態建一般表友善太多。

避免不必要的 Recompile#

Fritchey 提出的實務原則：

• 不要把 DDL 與 DML 交錯
• 減少統計變動引發的 recompile
• 用 KEEPFIXED PLAN hint
• 對特定表停用自動統計維護
• 用 table variable 取代 temp table（特定情境）
• 跨範圍重用 temporary table
• 不要在批次中改 SET 選項

避免 DDL / DML 交錯#

CREATE OR ALTER PROC dbo.TempTable
AS
CREATE TABLE #MyTempTable (...);
-- 第一次 recompile：deferred compilation
INSERT INTO #MyTempTable ... ;
SELECT ... FROM #MyTempTable ... ;

CREATE CLUSTERED INDEX iTest ON #MyTempTable (ID);
-- 又 recompile：schema 變更
SELECT ... FROM #MyTempTable ... ;

CREATE TABLE #t2 (c1 INT);
-- 又 recompile
SELECT c1 FROM #t2;

改寫做法：把所有 DDL（CREATE TABLE / INDEX）集中在 procedure 開頭，一次完成所有結構，再進入 DML 階段。recompile 可以從 4 次降到 1 次。

KEEPFIXED PLAN#

SELECT ... FROM dbo.Test1
WHERE C1 = 1
OPTION (KEEPFIXED PLAN);

統計改變時 不再觸發 recompile——計畫被保留。

hint 是命令不是建議。KEEPFIXED PLAN 可能讓糟糕的計畫被保留下去。只在驗證確實有益後使用。

KEEP PLAN 是另一個類似 hint，只針對暫存表——直到統計門檻 500 列才更新，可避免 temp table 在小資料量內反覆 recompile。

對單表停用自動統計維護#

EXEC sys.sp_autostats 'dbo.Test1', 'OFF';

等同把該表的統計凍結。長期不更新統計 會讓所有引用此表的查詢估算崩潰。如果關掉，必須搭配人為的定期統計更新計畫。

Table Variable#

@TempTable 與 #TempTable 幾乎一樣，但 table variable 沒有統計。沒統計就不會因「統計過時」而 recompile。

DECLARE @TempTable TABLE (C1 INT PRIMARY KEY);

對於迴圈中反覆 INSERT 少量資料、不需要統計支援的場景，table variable 可顯著減少 recompile。

Table variable 的 cardinality 估算固定為 1（或新版部分情境改進）。資料量大時會選錯計畫，反而比 temp table 慢。

經驗法則：

• 小量資料、用作參數傳遞 → table variable
• 中大量資料、需要 join 與索引 → temp table

跨範圍 / 不要動 SET 選項#

• 把 temp table 在外層批次建立，子 procedure 引用同一個——避免每次都做 deferred compilation
• 同一連線內保持一致的 SET 選項，避免「同個查詢但 SET 不同 → 不同計畫 → 反覆 recompile」

本章定調#

• Recompile 不是壞事，但 過度 recompile 是。
• 用 Extended Events + Causality Tracking 精準找出哪一條敘述被 recompile、為什麼。
• 多數 recompile 來自 schema 變更與統計更新。
• 改善的關鍵是 改寫程式碼結構：DDL / DML 分離、用對的中介物件（temp vs. table variable）、避免動態 SET。
• query hint（KEEPFIXED PLAN / KEEP PLAN）是最後手段，先測過再用。

下一章將進入 索引架構——理解 rowstore 與 columnstore 的內部結構，是後續所有索引調校的基礎。