使用 OPTION RECOMPILE 但不使用 OPTION OPTIMIZE FOR 時選擇日期索引 SEEK

我有一張包含 10 年“包裹掃描”的表格。有人掃描一個包裹，它會記錄日期和使用者名。現在讓我們假設保留 10 年的數據實際上是有目的的。

我有一個頁面顯示過去一周的摘要，所以很明顯我只想閱讀 1 週的數據。

這是要在 SSMS 中執行兩次的查詢，一次使用硬編碼的最近日期，另一次使用2013 中的舊日期。它最初是一個參數化查詢，但在 SSMS 中我用@p0日期替換：

SELECT [t0].[VerifyDate], [t0].[PackageId], [t0].[Username]
FROM [dbo].[PackageVerification] AS [t0]
INNER JOIN [dbo].[Package] AS [t1] ON [t1].[PackageId] = [t0].[PackageId]

WHERE ([t1].[PackageStatus] <> 99) AND ([t0].[VerifyDate] > @p0)   
ORDER BY [t0].[VerifyDate] DESC

在我執行它之前，我想介紹一下我的日期索引。

現在我的日期索引不在我的PackageVerification桌子上，而是在一個“幫助視圖”上，它執行上面看到的相同連接。上面的查詢能夠神奇地使用這個索引視圖，因為我啟用了 SCHEMABINDING。

CREATE NONCLUSTERED INDEX [IX_Helper_PackageVerification_USER_SCAN_HISTORY] ON [dbo].[Helper_PackageVerification]
(
   [VerifyDate] DESC,
   [PackageStatus] ASC
)
INCLUDE (
   [VerifyDateDate],
   [Username]
) 

當我在 SSMS 中使用舊日期和新日期執行查詢時，它會按預期使用掃描或搜尋。門檻值似乎在 2015 年左右。所以任何最近的東西都絕對應該使用搜尋。這是結果：

當我從我的應用程序將它作為參數化查詢執行時，我總是得到一個完整的掃描，由於某種原因它使用了一個並行計劃。

至少它使用了我的輔助索引。

我實際上不確定為什麼我沒有對此進行參數嗅探。我總是通過一個最近的日期，所以我認為它可能更喜歡掃描，但考慮到情況，我可以選擇上述計劃。有一百萬多行，大約需要 150 毫秒。

順便說一下，這是一個帶有 2vCores 的 SQL Azure 數據庫。參數嗅探已啟用，參數化設置為simple。

如果我更改查詢並使用我執行我的應用程序，OPTION (RECOMPILE)我確實獲得了所需的SEEK和僅幾毫秒的非常好的性能。重新編譯時間似乎可以忽略不計，坦率地說，這是我可以使用的非常好的性能。

當我查看查詢儲存時，我可以驗證 OPTION RECOMPILE 使用查找最近的日期，並掃描舊日期！驚人的。

但是，我以前從未嘗試過這個 - 我想如何進一步改進它OPTION (OPTIMIZE FOR @p0 = '4/1/2021')。

我希望這也可以使用搜尋，但不需要每次都重新編譯。我只是定期更改傳遞給 OPTIMIZE FOR 的日期 - 可能是上個月的開始。

但是，這是查詢儲存中的查詢。

當將日期參數設置為 21 年 4 月 7 日時，它會全面掃描所有 1+ 百萬行！

所以現在我迷路了。我已嘗試閱讀有關該主題的所有內容，但沒有遇到此問題。RECOMPILE 有效，但是當我期望 OPTIMIZE FOR 有效地模擬使用硬編碼值在 SSMS 中執行查詢時，它似乎沒有做任何事情。

查詢計劃

這第一個計劃是唯一出乎意料的計劃——它是一個掃描，我想要一個搜尋。

優化@p1 = ‘2021/4/1’ - https://www.brentozar.com/pastetheplan/?id=H1JB43AUu 優化兩個參數 - https://www.brentozar.com/pastetheplan/?id=rkV9U3AUu 選項重新編譯 - https://www.brentozar.com/pastetheplan/?id=SJ5cS3CUd

這些是為了證明優化器知道最近的日期應該是一個搜尋！

硬編碼 2013 - 掃描 - https://www.brentozar.com/pastetheplan/?id=BkeA42RLu 硬編碼 2015 - 搜尋 - https://www.brentozar.com/pastetheplan/?id=S1c8r3R8O

我開始懷疑這個版本是否不支持 OPTIMIZE FOR，即使我找不到任何說它不支持的東西

---

**編輯：（**在保羅的回答之後）

我嘗試了一些額外的東西。首先是我之前沒有包含的 VIEW 定義。這是一個 JOIN 並且因為它使用 SCHEMABINDING 優化器能夠代替它：

創建視圖

$$ dbo $$.$$ Helper_PackageVerification $$ 使用架構作為 選擇

– 包驗證欄

$$ t0 $$.PackageVerificationId, $$ t0 $$.Verfied，——很久以前的拼寫錯誤！ $$ t0 $$.VerifyDate, – 這是不可為空的$$ t0 $$順便提一句 $$ t0 $$。使用者名， – 包列

$$ t1 $$.PackageId, $$ t1 $$.PackageStatus, $$ t1 $$.PackedOnDate 從

$$ dbo $$.$$ PackageVerification $$作為$$ t0 $$

內部聯接$$ dbo $$.$$ Package $$作為$$ t1 $$在$$ t1 $$.$$ PackageId $$=$$ t0 $$.$$ PackageId $$ WHERE (Verfied = 1 AND VerifyDate IS NOT NULL AND PackageStatus <> 99) 去

CLUSTERED 索引已打開PackageVerificationId，主 NON CLUSTERED 索引如上所示。我實際上創建了六個轉換索引來查看它會選擇哪個。

1. 我硬編碼PackageStatus <> 99. 它最初是一個參數。
2. 我嘗試將 NOT NULL 添加到視圖上的過濾器以查看會發生什麼。這確實給了我一個 SEEK，但是一個沒用的，因為 SEEK 謂詞實際上是 on VerifyDate IS NOT NULL。

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

您無法將過濾索引添加到索引視圖，因此即使視圖過濾掉 NOT NULL 日期，它也可能無法匹配。所以這可能是我無法讓我的日期用於 SEEK 謂詞的最終原因？

2. 在這種情況下，我沒有嘗試直接在查詢中使用輔助索引，但我非常希望它可以與 NOEXPAND 一起使用，因為我正在其他地方這樣做。

使用OPTIMIZE FOR不一樣OPTION (RECOMPILE)。前者在基數估計中使用提供的參數值，用於可能與其他參數值一起重用的計劃。recompile 選項嵌入執行時參數值，並生成一個永遠不會被重用的一次性計劃。

因此，該OPTIMIZE FOR計劃需要確保所有可能值的正確操作。重新編譯計劃可以使用僅對目前值有效的附加優化。它還可以使用僅適用於文字值的優化，例如將過濾器推過視窗函式。

這對您的情況很重要，因為當計劃與索引視圖匹配時，它會在VerifyDate和PackageStatusOPTIMIZE FOR上添加額外的IS NOT NULL剩余謂詞：

重新編譯計劃可以刪除此邏輯，因為已知提供的值不為空。這些額外隱含謂詞的存在足以防止查找的索引匹配。通常最好確保源列被限制為不為空，或者在索引視圖定義中明確拒絕以最小化這種事情。

現在，優化器為您的查詢提供了多種計劃選擇。一個跡像是載入的統計對象的數量 - 17。通過優化器的路徑的微小差異會產生不同的結果。

從技術上講，自動索引視圖匹配是一個簡潔的功能，但它確實有局限性。SQL Server 需要添加一些東西並應用特定的重寫來實現匹配，這可能會產生意想不到的副作用（注意上面顛倒的@p1 謂詞）。匹配後計劃也不總是完全清理以匹配針對視圖編寫的查詢將產生的內容。這些不是錯誤，只是實現細節。

我通常建議人們直接針對視圖編寫查詢並指定一個NOEXPAND提示，這是可行的。您可能會發現以這種方式編寫的查詢測試會產生您正在尋找的結果。

我寫過的相關文章：

• 參數嗅探、嵌入和重新編譯選項
• 索引視圖和統計資訊
• 在企業版中使用 NOEXPAND 提示的另一個原因

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