大記憶體授予請求

我有一個帶有多個執行計劃的查詢，與第二個相比，授予一個計劃的記憶體很大

基於這篇文章https://blogs.msdn.microsoft.com/sql_server_team/addressing-large-memory-grant-requests-from-optimized-nested-loops/

當嵌套循環聯接的外部表具有將結果過濾為小輸入的謂詞時，就會出現此問題，但批處理排序似乎使用了與整個外部表等效的基數估計值。這可能會導致感知到過多的記憶體授予，這在非常並發的伺服器中可能會產生一些副作用，例如 OOM 條件、計劃記憶體驅逐的記憶體壓力或意外的 RESOURCE_SEMAPHORE 等待。我們已經看到與此模式匹配的單個查詢實際上如何在高端機器（1TB+ RAM）上獲得數 GB 的授予記憶體。

到目前為止，一個選項是使用跟踪標誌 2340 全域禁用此功能，如 KB 2801413 中所述。但是，在 SQL Server 2016 RC0 中，我們更改了行為以保持優化的優勢，但現在最大授權限制是基於在可用的記憶體授予空間上。這種改進還轉化為更好的可擴展性，從某種意義上說，可以用更小的記憶體佔用執行更多的查詢。我們正在考慮將此行為反向移植到即將推出的已將此行為移植到 SQL Server 2014 Service Pack 2，並像往常一樣為市場上的版本提供附加值。

這正是我所看到的，但是我使用的是 SQL Server 2016 Enterprise。

這些是執行計劃

https://www.brentozar.com/pastetheplan/?id=SJ0mYAy0b

https://www.brentozar.com/pastetheplan/?id=BJzutC1R-

我的問題是

1. 2個執行計劃的原因是什麼？
2. 優化器正在使用最高執行計劃，我強迫它使用較低的計劃，但一段時間後它又回到最高執行計劃，有什麼原因嗎？
3. 如何解決這個問題？這個問題導致應用程序崩潰（有很多RESOURCE_SEMAPHORE等待並且應用程序變得無響應）？我應該使用提示：DISABLE_OPTIMIZED_NESTED_LOOP還是 Trace Flag 2340？

**注意：**我檢查了 XML 並且兩個計劃都有 NestedLoops Optimized="false"

1. 2個執行計劃的原因是什麼？

從sys.query_context_settings (Transact-SQL)文件中：

> > SQL Server 中有許多影響查詢語義（定義正確的查詢結果）的上下文設置。在不同設置下編譯的相同查詢文本可能會產生不同的結果（取決於基礎數據）。 > > >

看起來很可能相同的查詢文本是從具有不同上下文設置的會話中送出的。另請參閱應用程序慢，SSMS 快？作者：厄蘭·索馬斯科格。

2. 優化器正在使用頂層執行計劃，我強迫它使用較低的計劃，但一段時間後它又回到頂層，有什麼原因嗎？

計劃仍將不時根據圍繞更改的正正常則重新編譯，例如基礎對象、統計資訊、模式。正如sys.query_store_plan (Transact-SQL)所指出的，強制執行查詢儲存計劃有助於確保使用的計劃至少在根本上類似於源計劃：

> > 強制機制不保證該計劃將用於query_id引用的查詢。強制計劃會導致再次編譯查詢，並且通常會生成與plan_id引用的計劃完全相同或相似的計劃。 > > >

每次（重新）編譯都可能產生具有不同估計（包括記憶體授予）的計劃，具體取決於編譯時優化器可見的查詢中的任何值。不能使用查詢儲存或計劃指南強制授予特定的記憶體。

3.如何解決這個問題？

正如 Dan Guzman 的回答所暗示的，如果計劃對特定參數值非常敏感，那麼在每次執行時使用OPTION (RECOMPILE)提示編譯一個新計劃可能是最好的實用解決方案。

也就是說，可用於影響更新端執行計劃的工具很少，這在涉及級聯外鍵和索引視圖等事情時可能很複雜。如果一切都失敗了，您可能會發現沒有排序的執行計劃（以索引鍵順序對行進行排序）產生最佳結果。我知道沒有記錄的方式可以在沒有這些類型的情況下生成計劃，但是您可以嘗試強制使用未記錄的跟踪標誌 8795獲得的計劃。

您也可以嘗試使用合適的MAX_GRANT_PERCENT 查詢提示或簡單地安裝（或為 SQL Server 提供）足夠的記憶體來處理您的工作負載。

兩個計劃都具有Optimized="false"內部連接並且具有相同的形狀。我從引用的部落格文章中了解到，記憶體授予問題（外部表估計用於授予）僅適用於Optimized="true". 此外，對於嵌套循環外部表（帶有已刪除課程 ID 的表假離線），這些計劃的行數都為 1。

我懷疑經典的參數嗅探。在 CoursePrerequisiteAssignment 聚集索引刪除（級聯刪除？）之前，嵌套循環運算符的估計行數在頂部計劃中約為 2500 萬，而在底部計劃中為 240。這可能是驅動所需記憶體的原因。

OPTION(RECOMPILE)可能是最簡單和最好的解決方案。另外，索引調整可能有助於避免CoursePrerequisiteAssignment每次刪除課程時對錶進行全盤掃描。

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