為什麼 OPTION RECOMPILE 會導致謂詞下推？

我有一個 SQL 查詢，它是由一堆嵌套的視圖和表值函式組成的，至少有 4 層深（我沒有時間或耐心看完這一切，它有數百行程式碼在每個級別）。

我一直在試圖理解為什麼當我使用 OPTION (RECOMPILE) 執行基本查詢時它執行得非常快，但是當我在沒有這個 OPTION 的情況下執行它時，它執行得非常慢。

我已經確保在發生這種情況之前清除計劃記憶體，即使在生成新計劃時，它也不是最理想的，但是，OPTION (RECOMPILE) 的速度很快。

我檢查了這兩個計劃，並註意到對於帶有 OPTION (RECOMPILE) 的計劃，傳遞的參數。

SELECT [p].[Activity]
   ,[p].[ActivityType]
   ,[p].[Company]
   ,[p].[Flags]
   ,[p].[Id]
   ,[p].[Name]
   ,[p].[Priority]
   ,[p].[Filters]
   ,[p].[Priority]
   ,[p].[Classification]
   ,[p].[Number]
   ,[p].[TaskFilter]
   ,[p].[TaskType]
   ,[p].[User]
FROM (
   SELECT *
   FROM [ActivProdStatuses]('ProdJobTask', 0)
   ) AS [p]
WHERE (
       (   ([p].[User] = 'some_value') AND (([p].[Flags] & 8) = 0) ) 
          AND ([p].[Activity] = 'unique_value') 
       )
   AND  
     (CASE 
       WHEN ([p].[Flags] & 4) <> 0
        THEN CAST(1 AS BIT)
        ELSE CAST(0 AS BIT)
      END = 1 )
ORDER BY [p].[Priority]
OPTION (RECOMPILE)

在沒有 OPTION RECOMPILE 的計劃中，我的計劃中有一部分時間花在移動無用數據上，這些數據後來被 FILTER 運算符過濾（您可以看到從 FILTER 中出來的 0B）。

FILTER 運算符具有過程 (unique_value和some_value) 附帶的所有過濾參數以及在嵌套級別中確定的一些其他過濾參數。嵌套級別本身包含其他 TVF，其參數由 OUTER APPLY’ed 查詢確定。

在 OPTION RECOMPILE 版本中，這些參數被下推到執行計劃（我猜它被稱為 Predicate Pushdown）並在第一步中直接過濾，當從磁碟讀取數據時。

從我所看到的來看，這是我對為什麼會發生這種情況以及為什麼 OPTION RECOMPILE 計劃更有效的結論。您可以在下面看到同一張表的相同數據訪問以及更有效的 OPTION RECOMPILE 計劃的一部分。

---

現在，我的問題是，為什麼 OPTION RECOMPILE 計劃的行為與為新查詢生成新計劃時的行為不同，並且傳遞給它的參數/值相同。選項重新編譯有什麼作用？

我試圖在網上搜尋，看看它是否“強制”謂詞下推，但我找不到任何具體的資訊。

你可以在這裡找到緩慢執行的****匿名計劃。此外，OPTION RECOMPILE 執行的匿名計劃在這裡。

我嘗試使用 OPTION (RECOMPILE) 執行一次查詢，然後刪除提示並立即再次執行查詢（兩次參數相同）。第一次跑得快，第二次跑得慢。

我確實考慮過生成“快速”計劃，希望它能夠儲存在記憶體中並在不再指定 OPTION RECOMPILE 時得到重用。但是，我相信查詢計劃雜湊值不同，並且記憶體計劃不會被重用，因為除了傳遞給過濾子句的值之外，還有更多更改。

現在，我的問題是，為什麼 OPTION RECOMPILE 計劃的行為與為新查詢生成新計劃時的行為不同，並且傳遞給它的參數/值相同。選項重新編譯有什麼作用？

主要OPTION (RECOMPILE)做的是為任何參數的目前值編譯一個計劃，而不是重用任何記憶體的計劃。新生成的計劃不會被記憶體以供重用。

它啟用的第二件事是Parameter Embedding Optimization。SQL Server 在優化之前將任何參數替換為其文字值。

這可能聽起來微不足道，但可以實現重要的簡化。例如，值所在的任何類型轉換或複雜表達式都可以提前求值（常量折疊）。請注意CONVERT_IMPLICIT螢幕截圖中的將提供的varchar值轉換為nvarchar。我注意到您的數據庫已啟用強制參數化。

你的計劃很大而且是匿名的，但我建議參數嵌入和它可以實現的主要簡化是性能顯著提高的原因。

當您執行相同的查詢而不OPTION (RECOMPILE)進行相同的簡化時是不可能的，因為 SQL Server 無法安全地嵌入參數值，因為計劃可能會重複用於不同的值。

更多資訊和背景在我的文章（上面連結）參數嗅探、嵌入和重新編譯選項。它包含一個工作範例，逐步顯示參數嵌入和常量折疊如何工作以改進執行計劃。

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