查詢計劃未重複用於相同的查詢 sp_executesql vs adhoc
我的應用程序中的一個查詢是超時。使用 SQL Server 分析器，我能夠得到準確的錯誤 SQL 程式碼。這是一個
sp_executesql動態語句，在伺服器上的 SSMS 中執行 > 2 分鐘。相比之下，執行相同的 SQL ad hoc（在外部sp_executesql但仍帶有參數）只需要 600 毫秒。查看記憶體的執行計劃，這 2 個查詢具有完全相同的查詢雜湊但不同的計劃。我 DBCC FREEPROCCACHE 對準備好的（慢的）進行了 DBCC FREEPROCCACHE，希望接下來的執行將重用計劃用於即席快速查詢。但事實並非如此。執行 sp_executesql 查詢總是重新彈出一個新的、緩慢的計劃。
按照此處的指南，我研究了參數嗅探的可能性。才發現我的4個參數在兩個執行計劃中完全一樣。
我還嘗試
OPTION (RECOMPILE)了更新所涉及的表統計資訊，但沒有結果。執行計劃有 2 個非常相似的主要分支，除了較慢的分支在分支的交界處有嵌套循環，而快速的則沒有。較低的分支葉子是兩個計劃中的索引掃描，除了較慢的一個讀取數百萬行，而較快的一個讀取數千行。
較快的在索引的使用上沒有明顯的區別。頂部分支的組織方式略有不同。
準備好的查詢可以重用 Adhoc 記憶體的執行計劃嗎？什麼會導致性能出現如此巨大的差異？
準備好的查詢可以重用 Adhoc 記憶體的執行計劃嗎？
在這種情況下不是，因為
DECLARE語句的一部分是查詢文本的一部分。這與將參數傳遞給儲存過程或動態 SQL 時根本不同。您可能希望閱讀本文以更好地理解為什麼這些查詢不是“相同的”。
什麼會導致性能出現如此巨大的差異？
看來您遇到了與此問答相反的問題：
由於該計劃最初是使用相同的參數集編譯的，因此重新編譯提示不會改變任何內容。您在初始編譯中得到的錯誤猜測會延續到進一步的編譯中。
由於您沒有以允許任何其他回饋的方式發布查詢計劃，因此一些有限的想法可能會有所幫助：
- 更新此查詢中涉及的表/索引的統計資訊
- 向查詢添加
OPTION(HASH JOIN)提示- 向查詢添加
OPTION(OPTIMIZE FOR UNKNOWN)提示在沒有完全理解影響的情況下添加查詢提示時要謹慎，並且請不要將這些提示添加到其他查詢中，希望“它適用於這個查詢，因此它也適用於其他任何地方”。