計劃中計算標量運算符後的錯誤行估計

我很難理解執行計劃中行估計的來源。

粘貼計劃連結

declare
@BatchKey INT = 1, @ParentBatchKey INT = 1,
@QuoteRef varchar(50) = 'Q00018249',
@MpanRef varchar(50) = '1425431100004'


SELECT DISTINCT
       ISNULL(c.ContractReference,-1) AS [ContractReference] ,
       ISNULL(d_cd.ContractDetailsKey,-1) AS [ContractDetailsKey] ,
       -1 AccountManagerKey,
       -1 SegmentationKey,
       ISNULL(d_tpi.TpiKey,-1) AS [TpiKey] ,
       ISNULL(d_cu.CustomerKey,-1) AS [CustomerKey] ,
       ISNULL(d_p.ProductKey,-1) AS [ProductKey] ,
       -1 as PayPointKey,
       -1 AS [GspBandingKey], --Not used in Junifer ESOB
       ISNULL(d_pps.[ProductPricingStructureKey],-1) AS [ProductPricingStructureKey],
       ISNULL(d_tou.TouBandingKey,-1) AS [PricingStructureBandingKey],
       -1 AS [VolumePointCategoryKey],
       ISNULL(d_ppc.PowerPeriodCategoryKey,-1) AS [PowerPeriodCategoryKey],
       ISNULL(d_pcat.[PriceComponentAggregationTypeKey],-1) AS [PriceComponentAggregationTypeKey],
       -1 AS [MarginRateBandingKey], --Not used in Junifer ESOB
       -1 AS [DuosUrcBandingKey], --Not used in Junifer ESOB
       -1 AS [ConsumptionToleranceKey],
       ISNULL(d_mp.MeterPointKey,-1) AS [MeterPointKey] ,
       ISNULL(d.DateKey,-1) AS [ForecastDateKey] ,
       -1 AS [ForecastEFADateKey], 
       ISNULL(d_cw.DateKey,-1) AS [ContractWonDateKey] ,
       ISNULL(f.SiteVolumeKwh,0) AS [SiteVolume] ,
       ISNULL(f.GspVolumeKwh,0) AS [GspVolume] ,
       ISNULL(f.NbpVolumeKwh,0) AS [NbpVolume],
       @BatchKey,
       @ParentBatchKey,
       CAST(f.ForecastKey as NVARCHAR(100)) AS [SourceId]
FROM 
       [Electricity].[Forecast] f 
             INNER JOIN Electricity.ContractMeterPoint cmp ON cmp.MeterPointKey = f.MeterPointKey and cmp.ContractKey = f.ContractKey  
             INNER JOIN Electricity.Contract c on c.ContractKey = cmp.ContractKey 
       INNER JOIN Electricity.MeterPoint mp ON mp.MeterPointKey = cmp.MeterPointKey

       --INNER JOIN Electricity.ContractMeterPoint cmp ON cmp.MeterPointKey = mp.MeterPointKey and cmp.ContractKey = c.ContractKey 
       INNER JOIN Electricity.ProductBundle pb ON c.ProductBundleKey = pb.ProductBundleKey
       LEFT JOIN Electricity.Quote q ON c.QuoteKey = q.QuoteKey
       LEFT JOIN Gdf.Tender t ON q.TenderKey = t.TenderKey
       LEFT JOIN Gdf.Customer cu ON q.CustomerKey = cu.CustomerKey
       LEFT JOIN Electricity.ProductBundleAggregationType pbat ON pbat.ProductName = pb.BundleName
       LEFT JOIN Dimensional_DW.DimensionElectricity.Product d_p ON d_p.ProductDurableKey = pb.ProductBundleKey
       LEFT JOIN Dimensional_DW.Dimension.Tpi d_tpi ON d_tpi.TpiDurableKey = c.TpiKey
       LEFT JOIN Dimensional_DW.DimensionElectricity.ProductPricingStructure d_pps ON d_pps.ProductPricingStructureDurableKey = f.PriceStructureKey
       LEFT JOIN Dimensional_DW.DimensionElectricity.TouBanding d_tou ON d_tou.TouBandingDurableKey = f.PriceRateKey
       LEFT JOIN Dimensional_DW.DimensionElectricity.MeterPoint d_mp ON d_mp.MeterPointDurableKey = cmp.MeterPointKey
       LEFT JOIN Dimensional_DW.DimensionElectricity.PriceComponentAggregationType d_pcat ON d_pcat.[TnuosAggregationType] =pbat.[TNUoSAggType] AND d_pcat.[DuosAggregationType] =pbat.[DUoSFixedAvailAggType] AND d_pcat.[DuosUrcAggregationType] =pbat.[DUoSURCAggType] AND d_pcat.[BsuosAggregationType] =pbat.[BSUoSAggType] AND d_pcat.[ROAggregationType] =pbat.[ROAggType]
       LEFT JOIN Dimensional_DW.Dimension.Date AS d ON d.DateKey = CAST(CONVERT(NVARCHAR(8), f.DeliveryDate, 112) AS INT) 
       LEFT JOIN Dimensional_DW.Dimension.Date AS d_cw ON d_cw.DateKey = CAST(CONVERT(NVARCHAR(8), c.QuoteWonDate, 112) AS INT) 
       LEFT JOIN Dimensional_DW.DimensionElectricity.PowerPeriodCategory d_ppc ON d_ppc.HhPeriod = f.Period
       LEFT JOIN Dimensional_DW.Dimension.Customer d_cu ON d_cu.CustomerDurableKey = cu.CustomerKey
       LEFT JOIN Dimensional_DW.DimensionElectricity.ContractDetails d_cd ON d_cd.ContractDetailsDurableKey = c.ContractKey

WHERE  1=1
  and     c.ContractReference = @QuoteRef
  and c.QuoteWonDate IS NOT NULL 
  and c.QuoteKey <> -1
          --(SELECT distinct C.ContractKey FROM Electricity.Contract WHERE ContractReference = @QuoteRef and c.QuoteWonDate IS NOT NULL and c.QuoteKey <> -1)
               --(SELECT distinct C1.ContractKey FROM Electricity.Contract c1 WHERE c1.ContractReference = @QuoteRef and c1.QuoteWonDate IS NOT NULL and c1.QuoteKey <> -1)
       and mp.MpanID = @MpanRef
             --and c.ContractKey = 18235
             --and d.DateKey =  20180522
             order by [ForecastDateKey]

我的問題是圍繞標量運算符 nodeId 26：

我不確定如何生成 5 的行估計值 - 這似乎隨後將計劃級聯到大多數其他運算符 - 嵌套循環運算符估計的執行計數在計劃中進一步下降似乎都表明估計為 ~5，然後 ~實際35k。

為什麼要為標量運算符提供大約 14000 行的估計值，然後估計輸出為 5？這是一個問題還是一個紅鯡魚？它與它正在執行的轉換有什麼關係嗎？我可以理解影響連接，但為什麼會影響轉換的輸出？

為什麼要為標量運算符提供大約 14000 行的估計值，然後估計輸出為 5？這是一個問題還是一個紅鯡魚？

這是違反直覺的，但查詢優化器探索計劃空間的方式的自然結果。當它為特定計劃運算符或子樹生成新的、邏輯等效的替代方案時，它可能需要推導出新的基數估計。

由於估計是一個統計過程，因此不能保證在邏輯等效（但物理上不同）的樹上得出的估計會產生相同的數字，事實上在大多數情況下，它們不會。通常沒有明顯的方法來偏好一種估計而不是另一種估計。

當優化到達終點時，找到的最佳物理替代方案被“縫合在一起”以形成最終計劃。結果，該計劃可能會出現“不一致”，這僅僅是因為在不同時間對不同邏輯結構進行了估算。例如，一個計算標量可能一開始是一個邏輯聚合，後來被簡化了。

我在我的文章Indexed Views and Statistics中寫了更多關於此的內容。

如果您懷疑基數錯誤估計正在影響計劃選擇（以一種重要的方式），您可以選擇手動拆分查詢或使用提示。將節點 27 或其附近的小型中間集具體化為臨時表可能會很好地提高計劃質量，因為優化器可以在該點看到準確的基數並創建自動統計資訊。查詢編寫器還可以選擇向臨時表添加索引。

它與它正在執行的轉換有什麼關係嗎？我可以理解影響連接，但為什麼會影響轉換的輸出？

通常不會，不會，但最好盡可能避免轉換。當然，轉換會影響基數估計，但幾乎沒有跡象表明這是這裡的原因。

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