SQL Server 快取計畫分析：找出佔用記憶體高的 Ad-hoc 查詢計畫

記憶體壓力大時，檢查計畫快取中佔用空間大、重用率低的 Ad-hoc 查詢計畫，可指導參數化強制或優化索引策略。

參數化查詢腳本

DECLARE @objtype VARCHAR(20) = 'Adhoc';  -- 可改 'Proc', 'Prepared'
DECLARE @usecounts INT = 1;
DECLARE @size_in_bytes INT = 1;

SELECT TOP 1000
    usecounts AS '快取次數',
    size_in_bytes/1024 AS '佔用MB',
    cacheobjtype AS '快取類型',
    objtype AS '物件類型',
    b.text AS '查詢語句'
FROM sys.dm_exec_cached_plans a
CROSS APPLY (
    SELECT plan_handle, qt.text
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
) b
WHERE a.plan_handle = b.plan_handle
  AND objtype = @objtype
  AND usecounts = @usecounts
  AND size_in_bytes > @size_in_bytes
ORDER BY size_in_bytes DESC;

分析重點

• usecounts = 1 且佔用大：典型的單用計畫浪費記憶體，建議啟用「優化針對臨時工作負載」或強制參數化。
• objtype = 'Adhoc'：動態 SQL 未參數化產生的計畫。
• 對比 Proc（預存程序）與 Prepared（準備陳述式）的快取分布。

定期清理或優化高佔用低重用計畫，可釋放計畫快取空間，減少編譯鎖競爭。