查詢計劃記憶體因臨時查詢而膨脹，即使使用“針對臨時工作負載進行優化”也是如此

我一直注意到我們的查詢計劃記憶體存在我認為不尋常的問題，記憶體中的計劃從未超過一天。

通過執行以下查詢（由Kimberly Tripp提供），它表明大多數計劃（6Gb 記憶體計劃中的 4.5Gb 或 ~50000 中的 44813 個）是使用計數為 1 的即席查詢。

SELECT objtype AS [CacheType]
   , count_big(*) AS [Total Plans]
   , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
   , avg(usecounts) AS [Avg Use Count]
   , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
   , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

我已經確定了問題查詢（動態的EXEC，當然帶有 ……），這相當可怕，但“修復”也很複雜，所以我正在研究可以立即做出的改進。

該實例已設置為使用 Optimize for Ad-hoc Workloads，但是CacheObjTypefromsys.dm_exec_cached_plans都是Compiled Plan而不是Compiled Plan Stub。

使用 Ad-hoc Workload 模式時，計劃是否應該在它們大於 1之前不被編譯計劃存根？usecounts或者這不是它的工作原理？

在談論 Adhoc 查詢時，還有refcounts一個似乎沒有人提及的領域。當類型為Compiled Plan Stub時，refcounts 始終為 1，當類型為Compiled Plan 時，refcounts 始終為2 。即使通過閱讀BOL，我也不完全確定該欄位的含義。有人可以澄清嗎？

據此，即席批處理的第二次執行刪除了存根（僅使用過一次）並創建並記憶體了計劃（第一次使用它）。

refcounts除了記憶體對象的引用計數之外，我還沒有看到很多引用。Adhoc Compiled Plan 對象的 a 仍然可以refcount為 1，因此它不完全是由計劃的持久性引起的。

引用自：https://dba.stackexchange.com/questions/65237