使用 RECOMPILE 查詢提示時查詢之間執行時間的巨大差異

我在同一個 SQL Server 2005 實例上執行了兩個幾乎相同的查詢：

1. 第一個是SELECTLINQ 生成的原始查詢（我知道，我知道……我不是應用程序開發人員，只是 DBA :)。
2. 第二個和第一個完全一樣，OPTION (RECOMPILE)最後加了一個。

沒有其他任何改變。

第一個每次執行需要 55 秒。

第二個需要 2 秒。

兩個結果集是相同的。

為什麼這個提示會在性能上產生如此巨大的提升？

Books Online 上的條目RECOMPILE沒有提供非常詳細的解釋：

指示 SQL Server 數據庫引擎在查詢執行後放棄為查詢生成的計劃，強制查詢優化器在下次執行相同查詢時重新編譯查詢計劃。如果不指定 RECOMPILE，數據庫引擎會記憶體查詢計劃並重用它們。編譯查詢計劃時，RECOMPILE 查詢提示使用查詢中任何局部變數的目前值，如果查詢在儲存過程中，則將目前值傳遞給任何參數。

RECOMPILE 是創建使用 WITH RECOMPILE 子句的儲存過程的有用替代方法，當必須重新編譯儲存過程中的查詢子集而不是整個儲存過程時。有關詳細資訊，請參閱重新編譯儲存過程。當您創建計劃指南時，重新編譯也很有用。有關更多資訊，請參閱使用計劃指南優化已部署應用程序中的查詢。

OPTION (RECOMPILE)由於我的查詢有很多局部變數，我的猜測是當我使用查詢提示時，SQL Server 能夠（認真地）優化它。

我所看到的每一個地方，人們都在說OPTION (RECOMPILE)應該避免這種情況。對此的解釋通常是使用這個提示 SQL Server 無法重用這個執行計劃，因此每次都要浪費時間重新編譯它。
（但是）鑑於巨大的性能優勢，我傾向於認為這次使用此查詢提示將是一件好事。

我應該使用它嗎？如果沒有，有沒有一種方法可以強制 SQL Server 使用更好的執行計劃而無需此提示且無需更改應用程序？

如Microsoft SQL Server 2005 中查詢優化器使用的統計資訊一文中所述

如果您在查詢謂詞中使用局部變數而不是參數或文字，則優化器會採用質量降低的估計，或猜測謂詞的選擇性。在查詢中使用參數或文字而不是局部變數

當優化器對一列根本沒有可用的統計資訊時，它會猜測謂詞=將匹配 10% 的行、BETWEEN9% 和任何一個>, >=, < and <=將匹配 30%。如果有可用的列統計資訊，則=謂詞將被區別對待，如下所示。

即使在查詢中使用了局部變數，在相等謂詞的情況下也會使用比猜測更好的估計值。@local_variable = column_name使用 column_name 直方圖中的平均值頻率來估計“ ”形式的條件的選擇性。因此，例如，如果列 column_name 包含所有唯一值，則將使用 的選擇性估計1/(number of unique values in column)，這是準確的。

所以這本質上與使用 for 相同OPTIMIZE FOR (UNKNOWN)。它可能比簡單的10%猜測更準確，但它不是針對您查詢的特定值量身定制的。

要強制 SQL Server 在每次執行時優化查詢，並在優化查詢期間使用局部變數的值來估計基數和成本，請將RECOMPILE查詢提示添加到查詢中。

使用RECOMPILE您可能會獲得更準確的基數估計，因此具有連接順序/連接類型的不同計劃更適合從實際查詢的不同部分返回的行數。

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