為什麼這個儲存過程會導致聚集索引掃描，但在使用 OPTION RECOMPILE 時會查找？

我想我可能會根據我的研究知道答案，但我正在尋找關於引擎如何/為什麼按照它的方式編譯計劃的確認

傳入參數：@ID int ,@OtherID INT

SELECT  b.Column1
       ,b.Column2
       ,b.Column3
       ,b.Column4
       ,b.Column5
       ,c.Column1
       ,b.Column1
       ,e.Column1
FROM    Table1 AS b 
       inner join Table2 AS t
       on b.ID = t.ID
       left join [LINKED SERVER].[DB].dbo.Table3 as c
       on b.ID = c.ID
       left join Table4 AS e
       on b.ID= e.ID
where   (b.ID = @ID or @ID= 0)
       And b.ID = @OtherID
       And b.ID IS NOT NULL
       and e.ID = 1

現在我已經確定索引掃描的原因是因為這行：where (b.ID = @ID or @ID= 0). 更具體地說，@ID = 0。為了進一步澄清，該 ID 欄位的 0 在基礎表中不作為值存在，這只是開發人員允許使用者通過傳入拉回所有結果的操作0 到參數，然後檢查該參數是否為 0，因此結果會拉回更多行（通常，您只會返回 1-3 個結果）。

現在，非常奇怪的是，如果我添加OPTION RECOMPILE，引擎當然能夠以成本（編譯時間）為代價創建一個更好的計劃：

我想知道這怎麼可能。根據我在網上閱讀的內容，通過 using OPTION RECOMPILE，引擎將用傳遞給參數的實際值替換該值，並且可以很容易地看到 @ID 1234 不等於 0。但是，如果您不使用OPTION RECOMPILE引擎將獲取記錄的總數，即 120,000，然後將其除以不同可能性的總數，即 107,000。這會返回大約 1.1 個估計行，我通過查看具有索引掃描的計劃的估計屬性來確認這一點，但是如果估計正確，為什麼引擎會繼續索引掃描呢？為了確定，我什至更新了統計數據。

b.ID = @ID OR @ID = 0

優化器必須生成一個帶有索引掃描的計劃，因為該計劃是記憶體和重用的。

在隨後的執行中，該參數@ID可能為零。在這種情況下，索引搜尋沒有任何價值，因為沒有任何ID價值可以搜尋。其他時候，將為 提供一個非零值@ID，但記憶體計劃必須為所有可能的參數值正確工作。

使用OPTION (RECOMPILE)時，參數嵌入優化(PEO) 表示每次執行時使用目前值@ID代替參數，並且不記憶體任何計劃。

說@ID是 1234。在 PEO 之後，優化器看到：

b.ID = 1234 OR 1234 = 0

矛盾檢測邏輯將其簡化為：

b.ID = 1234

…這可以在ID.

如需進一步閱讀，請參閱我的文章Parameter Sniffing, Embedding, and the RECOMPILE Options。

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