Bad row estimate following Compute Scalar operator in plan - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Bad row estimate following Compute Scalar operator in plan

sql-server sql-server-2014 add tag

GeorgePalacios (imported from SE)

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

[Paste the plan link][1]


    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:

[![enter image description here][2]][2]

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?


  [1]: https://www.brentozar.com/pastetheplan/?id=HkWoHrY8Q "Paste the plan link"
  [2]: https://i.stack.imgur.com/zN1vX.png

Top Answer

Paul White (imported from SE)

>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][1].

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.


  [1]: https://sqlperformance.com/2014/01/sql-plan/indexed-views-and-statistics

1 Answer