為什麼遞歸 CTE 只估計 1 行？

給定兩個級聯、自包含（沒有真實表）的遞歸 CTE：

create view NumberSequence_0_100_View
as
with NumberSequence as
(
   select 0 as Number
   union all
   select Number + 1
     from NumberSequence    
    where Number < 100
)
select Number
 from NumberSequence;
go

create view NumberSequence_0_10000_View
as
select top 10001
      v100.Number * 100 + v1.Number as Number
 from Common.NumberSequence_0_100_View v100
cross join Common.NumberSequence_0_100_View v1
where v1.Number < 100
  and v100.Number * 100 + v1.Number <= 10000
   -- please resist complaining about "order by in view" for this question
order by v100.Number * 100 + v1.Number 
go

然後生成估計/實際計劃：

select * from NumberSequence_0_10000_View

估計 實際

執行時間 23 毫秒，但最終輸出僅估計一行（僅第一個視圖為 2 行）。

問題是，當它用作子查詢來連接真實數據時（例如“DaysAgo”），計劃通常是一個非常慢的嵌套循環，我經常需要添加一個連接提示/反向順序等。

無論如何，在保持 CTE 方法的同時改進估計嗎？是否曾經請求過“with (AssumeMinRows=N)”提示？對於許多情況（不僅僅是 CTE），這似乎是一個很好的通用助手。

為什麼遞歸 CTE 只估計 1 行？

遞歸公用表表達式的基數估計非常有限。

在原始基數估計模型下，估計是錨點和遞歸部分的基數估計的簡單總和。這相當於假設遞歸部分只執行一次。

在 SQL Server 2014 中，啟用新的基數估計模型後，邏輯略有改變，假設遞歸部分執行了 3 次，每次迭代返回的行數相同。

這兩個都是未經教育的猜測，因此使用遞歸 CTes 通常會導致質量較差的估計也就不足為奇了。更一般地說，估計遞歸過程的結果幾乎是不可能的，因此優化器甚至不會嘗試。使用特別簡單的遞歸結構並沒有改變這一點，顯然是為了產生序列號——優化器沒有邏輯來檢測這種模式。

在您的特定情況下，最終估計是一個，因為優化器會進一步猜測謂詞的選擇性，例如[Recr1007]<(100)（在節點 ID 3 的過濾器中）和([Recr1003]*(100)+[Recr1007])<=(10000)（在節點 ID 2 的嵌套循環連接上的剩余謂詞）。同樣，這些都是猜測，結果很不幸，儘管並不令人驚訝。

無論如何，在保持 CTE 方法的同時改進估計嗎？

不是我知道的。

是否曾經請求過“with (AssumeMinRows=N)”提示？

不是直接用這些術語。有很多關於實現 CTE的請求，如果這種實現伴隨著自動統計生成，這將有所幫助。我不會列出其他人，因為您似乎已經對大多數建議發表了評論 :)

也有關於選擇性提示的建議，但還沒有像這樣的東西進入產品。

正如對該問題的評論中所述，您現在最好的選擇是使用實數表，而不是使用遞歸 CTE 即時生成一個。第二種選擇是使用手動實現——一個臨時表——我相信你知道。

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