為什麼 SQL Server 在將結果選擇到標量變數時不使用 OPTION(RECOMPILE) 執行常量 (UNION ALL) 分支消除？

我們使用一些“聚合”視圖使用鑑別器從多個表中進行選擇（注意：這些視圖不是分區視圖，因為鑑別器不在基表中）。這通常在使用 時效果很好option(recompile)，因為查詢計劃程序將在選擇查詢計劃之前消除不可到達的union all路徑。

但是，當將結果選擇為標量變數時，這種恆定折疊優化似乎失敗了。將結果選擇到臨時表變數中不會對重新編譯進行反優化。

這是 SQL Server 2017 中的複制案例：

-- A table, don't need any data.
create table [test].test_table (col1 int, primary key (col1));

-- A simple 'aggregate' view. Using the same table here is irrelevant and,
-- while the view shows the scenario, it might not be required to reproduce the issue.
create view [test].test_view as
select col1, descrim = 1 from [test].test_table
union all
select col1, descrim = 2 from [test].test_table

正常查詢，這會導致優化的查詢計劃僅涉及其中一個union all分支：

declare @descrim int = 2;

select count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here "works"

但是，一旦使用“選擇到標量變數”，該計劃就會變得不優化，因為它不會消除未使用的聯合。（在查詢文本中使用文字值時，該計劃仍然正確優化。）

declare @descrim int = 2;
declare @brokeit int;

select @brokeit = count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here does NOT optimize plan for @descrim!

1. 這種去優化是“預期的”嗎？

2. 關於和/或選擇標量變數的這種顯著的去優化行為在哪裡option(recompile)記錄或以其他方式深入討論？

3. 有沒有一種簡單的方法可以在select @x = ..不使用臨時表（變數）的情況下獲得重新編譯優化的計劃？

雖然在查詢執行期間，這union all將阻止對輔助工件的實際 IO 訪問，但這仍然是查詢計劃生成的問題。在產生此問題的特定錯誤情況下，保留多個表以供考慮會阻止 SQL Server 選擇適當的搜尋計劃，並且生成的計劃選項在給定域中是非常糟糕的選擇。

第一個“好”計劃：

第二個也是“壞”的計劃：

這個“壞”計劃還有一個隱式轉換警告，讓我懷疑選擇到標量變數可能會繞過許多不同的優化 - 甚至option(recompile)完全忽略提示。

常量折疊在 SQL Server 中具有特殊的含義。它不直接涉及您的問題。參數嵌入優化(PEO) 和矛盾檢測結合起來為您提供了執行計劃的廣泛簡化。

在安全的情況下，PEO 將例如參數或局部變數的字面值嵌入到查詢文本中。其中一項要求OPTION (RECOMPILE)是指定的。這保證了生成的計劃永遠不會被重用，因此用嗅探文字替換非文字可能是安全的。

提示本身只提供每次執行都會生成一個新的OPTION (RECOMPILE)計劃，任何參數的嗅探值都將用於基數估計，並且生成的一次性計劃在執行後不會被記憶體以供重用。

PEO 最初是在 SQL Server 2008 中添加到產品中的，但由於可能出現不正確的結果，不久之後它就被禁用了。它在 SQL Server 2008 SP1 CU5（ Microsoft 部落格文章）中重新啟用。

使用 PEO 優化的查詢在查詢優化器看來就像是用文字而不是參數或變數編寫的查詢一樣。矛盾檢測可以刪除WHERE 0 = 1出現類似文字表達式的整個子句或關係運算符子樹。之所以存在這種工具，是因為自動化工具通常會生成這樣的 SQL。

應用 PEO 並不總是安全的，但沒有正式記錄例外情況。一個例外是發生變數賦值的地方（其他存在，例如參數出現在OPTIMIZE FOR子句中的地方）。我的理解是，變數賦值涉及大量複雜的遺留行為，偶爾會有奇怪的語義，出於向後兼容性的原因而保留。保證 PEO 在所有情況下都能正確執行是不切實際的，因此在這種情況下禁用它。

PEO 是一種機會主義工具，它超越了OPTION (RECOMPILE). 在許多情況下，它可以帶來顯著的性能優勢，但沒有正式記錄。人們可能會將其視為一項獎勵功能 - 得到它時很好，但如果失望，則不會退款。

在您的範例中，無法應用 PEO，啟動過濾器（已記錄）提供了對子樹執行的消除。“未優化”計劃中顯示的過濾器運算符是啟動過濾器，僅當啟動謂詞評估為true時才執行其子樹。

在 PEO 上下文中，缺少“尋求計劃”通常是由於優化只能在存在文字值時執行（由於安全問題或實施限制）。此文字可能出現在原始文本中，也可能已通過 PEO 替換。這方面的一個例子是優化規則SelOnSeqPrj，它允許謂詞ROW_NUMBER在安全時通過序列函式，但僅在文字值可用時。

問題中程式碼的SQL Server 2017 複製不會產生問題中提到的額外計算標量和隱式轉換。用於生成該計劃的查詢似乎與問題中給出的查詢不同。或者，實例或數據庫可能有一些未指定的重要配置或選項。無論如何，我無法重現它。

OPTION (RECOMPILE)提示永遠不會被忽略。查詢中唯一的隱式轉換是根據（而不是）的要求從to的內部bigint結果轉換。COUNT(*)``integer``COUNT``COUNT_BIG

根據問題背後實際應用程序的要求和限制，您可能需要使用動態 SQL 或其他一些解決方案。如果可以以適合我們問答格式的方式表達，請隨意提出有關潛在問題的潛在解決方案的新問題。

---

您的問題的簡要答案是：

1. 是的。
2. 我在Parameter Sniffing、Embedding 和 RECOMPILE Options中介紹了它。
3. 不。

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