如何查詢計劃記憶體以了解它的健康程度？

今天早上看著我的監控工具，我收到了一個警告 High Compiles

查詢計劃編譯通常應小於每秒批處理的 15%。較高的值表示計劃重用率較低，並且通常與高 CPU 相關，因為計劃編譯可能是 CPU 密集型操作。高編譯可能與低計劃記憶體命中率相關，並且可能是記憶體壓力的指標，因為可能沒有足夠的空間將所有計劃保存在記憶體中。

如果您看到持續高編譯，請執行快速跟踪並按記憶體未命中對結果進行排序，然後展開詳細資訊以查看實際編譯語句（SP:CacheMiss 事件，突出顯示）以及原因（子類）和過程（對象）。

在進行表面調查時，我注意到 CPU 沒問題，如下圖所示

但是，儘管我已將針對臨時工作負載的優化設置為1，但當我查看 SQL Server 記憶體使用情況時，我發現計劃記憶體仍使用其中的很多內容，而不是緩衝區記憶體。如下圖所示。

優化即席工作負載選項用於提高包含許多一次性即席批處理的工作負載的計劃記憶體效率。當此選項設置為 1 時，數據庫引擎會在第一次編譯批處理時在計劃記憶體中儲存一個小的編譯計劃存根，而不是完整的編譯計劃。這有助於通過不允許計劃記憶體充滿未重用的已編譯計劃來減輕記憶體壓力。

問題：

如何找出可以從計劃記憶體中刪除的內容？或者至少我將如何開始這項調查？

開胃菜

設置 SQL Server 選項optimize for ad hoc workloads並不是修復查詢計劃記憶體中的高重新編譯值的真正解決方案。

然而，當您的應用程序正在執行大量只執行一次且會污染（浪費）查詢計劃記憶體空間的臨時（因此得名）查詢時，這是一個很好的解決方案。例如，這可以是允許使用者動態選擇他們希望查看其結果的表列的應用程序。

主菜

當 SQL Server 數據庫引擎 (DBE) 執行查詢並且該查詢以前從未執行過時，數據庫引擎必須確定它將如何訪問數據。一旦 DBE 確定了訪問數據的最佳方式，它就會將此資訊儲存在查詢計劃記憶體 (QPC) 中，以便使用者在下次應用程序再次執行相同的查詢時受益（儘管可能稍微有點不同的值）。

每次應用程序需要執行語句時，SQL Server DBE 都會搜尋 QPC。如果 DBE 在 QPC 中找到適當的查詢計劃，那麼它將選擇該查詢計劃來檢索數據。但是，如果 DBE 無法確定 QPC 中的適當查詢計劃（未找到或達到查詢 QPC 的超時值），則 DBE 將創建一個新的查詢計劃。這是您正在觀察的結果編譯/s。

點心

然而，根本原因可能會有所不同。

SQL Server 可能處於記憶體壓力之下，無法在 QPC 中儲存足夠多的已編譯查詢計劃。DBE 將淘汰舊計劃並插入新計劃。（解決方案：為 SQL Server 實例添加更多記憶體）

應用程序確實生成了大量以前從未執行過的查詢和/或與儲存在 QPC 中的查詢計劃的值略有不同的查詢。（解決方案：消除應用程序的複雜性）

回答您的問題

1. 你不能。查詢計劃屬於查詢計劃記憶體。您可以清除 QPC 或讓 DBE 盡力而為。（可以使用DBCC FREEPROCCACHE(來實現查詢計劃的選擇性刪除，但我不建議這樣做。）
2. 確定 QPC 中儲存了哪些查詢計劃，並優化 SQL Server 實例的應用程序和/或記憶體設置。（請參閱Cigar Lounge中的腳本並根據sys.dm_exec_cached_plans文件中 plan_handle 列的描述與 sys.dm_exec_sql_text 和/或其他 DMV 加入）

雪茄休息室

以下查詢將列出儲存在 QPC 中的所有記憶體計劃，並且可以連結到其他相關 DMV 以檢索附加資訊：

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,   
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes   
FROM sys.dm_exec_cached_plans AS ecp   
JOIN sys.dm_os_memory_objects AS omo   
    ON ecp.memory_object_address = omo.memory_object_address   
    OR ecp.memory_object_address = omo.parent_address  
WHERE cacheobjtype = 'Compiled Plan';  
GO  

參考：sys.dm_exec_cached_plans (Transact-SQL) (Microsoft Docs)

為 SQL Server 記憶體的每個查詢計劃返回一行，以便更快地執行查詢。您可以使用此動態管理視圖來查找記憶體查詢計劃、記憶體查詢文本、記憶體計劃佔用的記憶體量以及記憶體計劃的重用計數。

參考資料

• 為什麼我不使用 SQL Server 選項“針對臨時工作負載進行優化”？（DBA 堆棧交換）
• sys.dm_exec_query_plan (Transact-SQL) (Microsoft Docs)
• sys.dm_exec_cached_plans (Transact-SQL) (Microsoft Docs)
• 排查計劃記憶體問題(Microsoft MSDN)
• sp_BlitzFirst® 結果：每秒高編譯次數(Brent Ozar)
• 強制查詢計劃(Microsoft Technet)

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