為什麼選擇此查詢的所有結果列比選擇我關心的一列更快？

我有一個查詢，其中 usingselect *不僅讀取次數少得多，而且使用的 CPU 時間也比 using 少得多select c.Foo。

這是查詢：

select top 1000 c.ID
from ATable a
   join BTable b on b.OrderKey = a.OrderKey and b.ClientId = a.ClientId
   join CTable c on c.OrderId = b.OrderId and c.ShipKey = a.ShipKey
where (a.NextAnalysisDate is null or a.NextAnalysisDate < @dateCutOff)
   and b.IsVoided = 0
   and c.ComplianceStatus in (3, 5)
   and c.ShipmentStatus in (1, 5, 6)
order by a.LastAnalyzedDate

這完成了 2,473,658 次邏輯讀取，主要在表 B 中。它使用了 26,562 個 CPU，持續時間為 7,965。

這是生成的查詢計劃：

在 PasteThePlan 上：https ://www.brentozar.com/pastetheplan/?id=BJAp2mQIQ

當我更改c.ID為*時，查詢完成了 107,049 次邏輯讀取，在所有三個表之間相當均勻地分佈。它使用了 4,266 個 CPU，持續時間為 1,147。

這是生成的查詢計劃：

在 PasteThePlan 上：https ://www.brentozar.com/pastetheplan/?id=SyZYn7QUQ

我嘗試使用 Joe Obbish 建議的查詢提示，結果如下：

select c.ID沒有提示：https

select c.ID ://www.brentozar.com/pastetheplan/?id=SJfBdOELm 有提示： https ://www.brentozar.com/pastetheplan/ ?id=B1W___N87

select *無提示：https

select * ://www.brentozar.com/pastetheplan/?id=HJ6qddEIm有提示：https ://www.brentozar.com/pastetheplan/?id=rJhhudNIQ

與沒有提示的版本相比，使用OPTION(LOOP JOIN)提示select c.ID確實大大減少了讀取次數，但它的讀取次數仍然是select *沒有任何提示的查詢的 4 倍。添加OPTION(RECOMPILE, HASH JOIN)到select *查詢中使其性能比我嘗試過的任何其他方法都要差。

使用 更新表及其索引的統計資訊後WITH FULLSCAN，select c.ID查詢執行得更快：

select c.ID更新前： https

select * ://www.brentozar.com/pastetheplan/?id=SkiYoOEUm更新前： https ://www.brentozar.com/ pastetheplan/?id=ryrvodEUX

select c.ID更新後： https

select * ://www.brentozar.com/pastetheplan/?id=B1MRoO487 更新後：https ://www.brentozar.com/pastetheplan/?id=Hk7si_V8m

select *``select c.ID在總持續時間和總讀取次數（select *大約有一半的讀取次數）方面仍然表現出色，但它確實使用了更多的 CPU。總體而言，它們比更新前要近得多，但計劃仍然不同。

在 2014 兼容模式和 2014 中執行的 2016 和 2014 出現了相同的行為。什麼可以解釋這兩個計劃之間的差異？可能是沒有創建“正確”的索引嗎？統計數據稍微過時會導致這種情況嗎？

我嘗試以多種方式將謂詞移動到連接的ON一部分，但查詢計劃每次都是相同的。

索引重建後

我重建了查詢中涉及的三個表上的所有索引。 c.ID仍在進行最多的讀取（是 的兩倍多*），但 CPU 使用率大約是*版本的一半。該c.ID版本還溢出到 tempdb中ATable：https://www.brentozar.com/pastetheplan/?id=HyHIeDO87

c.ID：https://www.brentozar.com/pastetheplan/?id=rJ4deDOIQ

*

我還嘗試強制它在沒有並行性的情況下執行，這給了我性能最好的查詢：https ://www.brentozar.com/pastetheplan/?id=SJn9-vuLX

我注意到在執行排序的大索引搜尋之後運算符的執行計數在單執行緒版本中只執行了 1,000 次，但在並行版本中執行的次數要多得多，在 2,622 到 4,315 次執行各種運算符之間。

確實，選擇更多列意味著 SQL Server 可能需要更加努力地獲得查詢的請求結果。如果查詢優化器能夠為這兩個查詢提出完美的查詢計劃，那麼期望SELECT *比從所有表中選擇所有列的查詢執行時間更長。對於您的一對查詢，您觀察到了相反的情況。比較成本時需要小心，但慢查詢的總估計成本為 1090.08 個優化器單元，而快速查詢的總估計成本為 6823.11 個優化器單元。在這種情況下，可以說優化器在估計總查詢成本方面做得很差。它確實為您的 SELECT * 查詢選擇了不同的計劃，並且預計該計劃會更昂貴，但這裡的情況並非如此。發生這種類型的不匹配的原因有很多，最常見的原因之一是基數估計問題。運營商成本主要由基數估計決定。如果計劃中關鍵點的基數估計不准確，則計劃的總成本可能無法反映現實。這是一個嚴重的過度簡化，但我希望它有助於理解這裡發生的事情。

讓我們首先討論為什麼SELECT *查詢可能比選擇單個列更昂貴。查詢可能會將一些覆蓋索引轉換為非覆蓋索引，這SELECT *可能意味著優化器需要做額外的工作來獲取它需要的所有列，或者它可能需要從更大的索引中讀取。SELECT *也可能導致需要在查詢執行期間處理的更大的中間結果集。通過查看兩個查詢中的估計行大小，您可以看到這一點。在快速查詢中，您的行大小範圍從 664 字節到 3019 字節。在慢速查詢中，您的行大小範圍為 19 到 36 個字節。對於具有較大行大小的數據，諸如排序或雜湊建構之類的阻塞運算符將具有更高的成本，因為 SQL Server 知道對大量數據進行排序或將其轉換為雜湊表的成本更高。

查看快速查詢，優化器估計它需要對 240 萬次索引搜尋Database1.Schema1.Object5.Index3。這就是大部分計劃成本的來源。然而，實際計劃顯示，該運算符僅進行了 1332 次索引查找。如果您將這些循環連接的外部部分的實際行與估計行進行比較，您會看到很大的差異。優化器認為需要更多的索引查找才能找到查詢結果所需的前 1000 行。這就是為什麼查詢具有相對較高的成本計劃但完成得如此之快的原因：被預測為最昂貴的操作員完成的工作不到其預期工作的 0.1%。

查看慢速查詢，您會得到一個主要包含雜湊連接的計劃（我相信循環連接只是為了處理局部變數）。基數估計肯定不是完美的，但唯一真正的估計問題就在排序的最後。我懷疑大部分時間都花在掃描具有數億行的表上。

您可能會發現向兩個版本的查詢添加查詢提示以強制執行與另一個版本關聯的查詢計劃很有幫助。查詢提示可能是一個很好的工具，可以用來找出優化器做出某些選擇的原因。如果您添加OPTION (RECOMPILE, HASH JOIN)到SELECT *查詢中，我希望您會看到與雜湊連接查詢類似的查詢計劃。我還預計雜湊連接計劃的查詢成本會更高，因為您的行大小要大得多。所以這可能就是為什麼沒有為SELECT *查詢選擇雜湊連接查詢的原因。如果您添加OPTION (LOOP JOIN)到僅選擇一列的查詢，我希望您會看到一個類似於SELECT *詢問。在這種情況下，減少行大小應該不會對整體查詢成本產生太大影響。您可能會跳過密鑰查找，但這只是估計成本的一小部分。

總之，我希望滿足SELECT *查詢所需的更大行大小將優化器推向循環連接計劃而不是雜湊連接計劃。由於基數估計問題，循環連接計劃的成本高於應有的成本。通過只選擇一列來減少行大小大大降低了雜湊連接計劃的成本，但可能不會對循環連接計劃的成本產生太大影響，因此您最終會得到效率較低的雜湊連接計劃。對於匿名計劃，很難說比這更多。

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