快取計畫兩大類別
- 編譯計畫：唯讀、所有使用者共享，包含查詢樹、最佳化策略。四種類型：
- Object Plans：預存程序、函數、觸發器
- SQL Plans：臨時查詢、自動參數化、sp_executesql
- Bound Trees：解析階段產生的綁定樹結構
- Extended Stored Procedures：擴展預存程序
- 執行內容：使用者專屬，含參數值、區域變數等執行時狀態，儲存於 SQL Manager Cache (SQLMGR)。
查詢快取計畫計數器
SELECT * FROM sys.dm_os_memory_cache_counters
WHERE name IN ('Object Plans', 'SQL Plans', 'Bound Trees', 'Extended Stored Procedures');
記憶體淘汰機制
- 記憶體壓力時，優先釋放 SQLMGR (執行內容)，因編譯成本為 0。
- 編譯計畫成本定期減半，歸零則釋放。
DBCC FREEPROCCACHE 可手動清除所有計畫 (慎用)。
程序快取大小動態計算公式
- Visible Target Memory：
DBCC MEMORYSTATUS 中 Buffer Counts 的 Visible 行值 (8KB 頁數)。
- 64 位元 OS：Target = Visible。
- 32 位元 OS：啟用 /3GB 參數 Target 最大 3GB，否則 2GB。啟用 AWE 時 Target ≠ Visible，快取不能使用 AWE 記憶體。
實測範例：32 位元 16GB RAM，AWE 配置 12GB 給 SQL Server
- Target = 1,572,864 頁 (12GB)
- Visible = 2GB (假設)
- 最大程序快取 = 2GB × 0.75 = 1.5GB
SP2 比 SP1 更嚴格限制快取大小，避免大量計畫擠佔資料緩衝區。
同一批次多重計畫成因
僅當執行環境改變時才產生新計畫。sys.dm_exec_plan_attributes 記錄計畫屬性 (set_options, language_id, date_format 等)。實測改變 SET DATEFORMAT 會產生新計畫：
監控與優化建議
- 定期查詢
sys.dm_exec_cached_plans 找出高佔用/重複計畫。
- 使用
OPTION (OPTIMIZE FOR...) 或 sp_create_plan_guide 固化參數敏感查詢計畫。
- 避免動態 SQL 產生大量單一使用計畫，改用參數化查詢或預存程序。
- 監控
Plan Cache Hit Ratio 低於 90% 時檢查參數化程度。
