查詢無限期執行 - 根本原因是什麼？（不問如何解決）

我有一個簡單的分組查詢，效果很好，直到我再添加一個連接：

select 
  [ca].Value,
  [c].cID, 
  [c].Name

from ReportingDB..Table1 [t1]

join MainDB..Companies [c] on
   [t1].CompanyID = [c].cID
   and [c].cID not in (1)

join MainDB..CompanyAttributes [ca] on -- this is the join that causes trouble
   [t1].CompanyID = caCID
   and caAttr = 26

group by [ca].Value, [c].cID, [c].Name

資訊：

Companies table 是一個“查找”表，有 2254 行，cID是 PK

CompanyAttributes與多對一關係Companies並有 4055 行

Table1與表有多對一關係Companies，有 3,485,150 行和

估計執行計劃看起來並不異常。

• 當我嘗試執行查詢時，它沒有完成，1 小時後我停止它，所以看不到實際執行計劃發生了什麼
• 實時查詢統計使我的 SSMS 掛起
• 如果刪除“group by”子句，它會非常快地開始毫無問題地獲取行。或者當最後一個連接被刪除時，它也可以正常工作 - 使用分組
• 伺服器不忙，有足夠的資源，啟動查詢時我沒有看到明顯的 CPU 上升
• 看sys.dm_exec_requests，wait_type 為 NULL，cpu_time並且logical_reads繼續增長，對於執行查詢的會話

原始查詢執行超過 1 小時而未完成的根本原因是什麼？

---

我自己解決了性能瓶頸（請參閱我的答案），但不明白究竟是什麼導致原始查詢執行 1 小時並且沒有在體面的伺服器上完成，查詢的表並不大。預計原始查詢將在不到 1 分鐘的時間內完成。

顯示的估計執行計劃的主要問題是 Table1 的聚集索引掃描上方的 Top 運算符。掃描有一個殘差謂詞：

[ReportingDB].[dbo].[Table1].[CompanyID]=[MainDB].[dbo].[CompanyAttributes].[cacID]

優化器嘗試估計在通過該測試之前需要從掃描中讀取多少行。它使用的邏輯是通用的，在我看來並不是特別合理。特別是，如果沒有匹配，掃描將執行完成，檢查所有 3,514,200 行。

更重要的是，此掃描將對公司屬性掃描返回的每一行重複，乘以公司搜尋返回的行數。這就是嵌套循環連接的工作方式。

優化器對在 Table1 的每次掃描中查找匹配項非常樂觀。這導致該計劃形狀具有所考慮的替代方案的最低估計成本。其根本原因是 Top 運算符引入的行目標。

如果您對 Top 的來源（不在您的查詢文本中）感到好奇，請查看與我密切相關的文章Row Goals, Part 4: The Anti Join Anti Pattern。

簡而言之：優化器引入了本地（部分）聚合作為其計劃搜尋的一部分。該聚合結果在邏輯上是多餘的，並被替換為等效的 Top。Top 的一個不幸的副作用是引入了行目標，從而顯著降低了掃描的估計成本。

帶有殘差謂詞的掃描上方的頂部（尤其是在重複掃描的情況下）是非常需要注意的反模式。

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