Vtyjkyuk

I'm struggling to understand where a row estimate is coming from in an execution plan.

Paste the plan link

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]

My problem is around nodeId 26, the scalar operator:

I'm unsure as to how the row estimate of 5 is being generated - this seems to then cascade down the plan to most other operators - the nested loop operators estimated execution counts further down the plan seem to all indicate ~5 estimated, then ~35k actual.

Why would the scalar operator be fed an estimate of ~14000 rows, then estimate an output of 5? Is this a problem or a red herring? Is it anything to do with the conversions it is performing? I can understand that affecting a join, but why would it affect the output of the conversion?

Why would the scalar operator be fed an estimate of ~14000 rows, then estimate an output of 5? Is this a problem or a red herring?

This is counter-intuitive, but a natural consequence of the way the query optimizer explores the plan space. As it generates new, logically-equivalent, alternatives for a particular plan operator or subtree, it may need to derive a new cardinality estimate.

Since estimation is a statistical process, there is no guarantee that estimates derived on logically-equivalent (but physically different) trees will produce the same number, in fact in the majority of cases, they won't. There is normally no obvious way to prefer one estimate over another.

When optimization reaches its end point, the best physical alternatives found are 'stitched together' to form the final plan. This plan can have 'inconsistencies' as a result, simply because estimates were computed on different logic structures at different times. For example, a Compute Scalar might have started out as a logical aggregate, which was later simplified.

I wrote more about this in my article Indexed Views and Statistics.

If you suspect the cardinality mis-estimate is affecting plan choice (in an important way), you may choose to split the query up manually or use hints. Materializing the small intermediate set at or around node 27 into a temporary table may well improve plan quality, since the optimizer can see accurate cardinality at that point and create automatic statistics. The query writer can also choose to add indexing to the temporary table.

Is it anything to do with the conversions it is performing? I can understand that affecting a join, but why would it affect the output of the conversion?

Not usually, no, though it is best to avoid conversions wherever possible. Certainly conversions can affect cardinality estimation, but there is little indication it is the cause here.