主页 / dba / 问题 / 215450
Accepted
George.Palacios
Asked: 2018-08-22 00:04:22 +0800 CST

计划中计算标量运算符后的错误行估计

  • 772

我很难理解执行计划中行估计的来源。

粘贴计划链接

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？这是问题还是转移注意力？这与它正在执行的转换有什么关系吗？我可以理解这会影响连接，但为什么会影响转换的输出？

sql-server sql-server-2014

1 个回答

  1. Best Answer

    为什么要向标量运算符提供约 14000 行的估计，然后估计输出为 5？这是问题还是转移注意力？

    这是违反直觉的，但却是查询优化器探索计划空间的方式的自然结果。当它为特定计划运算符或子树生成新的、逻辑上等效的备选方案时，它可能需要推导新的基数估计。

    由于估计是一个统计过程，因此不能保证从逻辑上等效（但物理上不同）的树得出的估计会产生相同的数字，事实上在大多数情况下，它们不会。通常没有明显的方法可以使一种估计优于另一种估计。

    当优化达到终点时，找到的最佳物理替代方案将“缝合在一起”以形成最终计划。结果，该计划可能存在“不一致”，原因很简单，因为估计是在不同时间根据不同逻辑结构计算的。例如，Compute Scalar 最初可能是一个逻辑聚合，后来被简化了。

    我在我的文章索引视图和统计中写了更多关于这个的内容。

    如果您怀疑基数错误估计正在影响计划选择（以一种重要的方式），您可以选择手动拆分查询或使用提示。将节点 27 处或周围的小型中间集具体化到临时表中可能会提高计划质量，因为优化器可以在该点看到准确的基数并创建自动统计信息。查询编写者还可以选择向临时表添加索引。

    这与它正在执行的转换有什么关系吗？我可以理解这会影响连接，但为什么会影响转换的输出？

    通常不，不，尽管最好尽可能避免转换。当然转换会影响基数估计，但几乎没有迹象表明这是这里的原因。

    • 8

相关问题