So I have an execution plan I'm trying to optimise, and there's a section of an operator I can't quite understand.
There is a section of the plan where a Compute Scalar is used, followed by an aggregation using a hash match.
I have a questions:
How can I use the properties of the hash match aggregator to tell me which columns it is outputting? I can't tell which columns the aggregation is being performed on.
This is the section of the plan:
This is the operator properties:
Plan XML for the element:
<RelOp NodeId="489" PhysicalOp="Hash Match" LogicalOp="Aggregate" EstimateRows="25432.8" EstimateIO="0" EstimateCPU="621.202" AvgRowSize="1038" EstimatedTotalSubtreeCost="12113" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="id" />
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="description" />
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="unitPrice" />
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="units" />
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="billCurrencyAmount" />
<ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="Item" />
<ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="InvoiceLineCategory" />
<ColumnReference Column="Expr1066" />
<ColumnReference Column="Expr1067" />
<ColumnReference Column="Expr1068" />
<ColumnReference Column="Expr1069" />
<ColumnReference Column="Expr1070" />
<ColumnReference Column="Expr1071" />
<ColumnReference Column="Expr1072" />
<ColumnReference Column="Expr1073" />
<ColumnReference Column="Expr1074" />
<ColumnReference Column="Expr1075" />
<ColumnReference Column="Expr1076" />
<ColumnReference Column="Expr1077" />
<ColumnReference Column="Expr1078" />
<ColumnReference Column="Expr1079" />
</OutputList>
<MemoryFractions Input="0.00395919" Output="0.379612" />
<Hash>
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="description" />
<ScalarOperator ScalarString="ANY([Staging].[Junifer].[BillBreakdownLine].[description] as [bbl].[description])">
<Aggregate Distinct="0" AggType="ANY">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="description" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="unitPrice" />
<ScalarOperator ScalarString="ANY([Staging].[Junifer].[BillBreakdownLine].[unitPrice] as [bbl].[unitPrice])">
<Aggregate Distinct="0" AggType="ANY">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="unitPrice" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="units" />
<ScalarOperator ScalarString="ANY([Staging].[Junifer].[BillBreakdownLine].[units] as [bbl].[units])">
<Aggregate Distinct="0" AggType="ANY">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="units" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="billCurrencyAmount" />
<ScalarOperator ScalarString="ANY([Staging].[Junifer].[BillBreakdownLine].[billCurrencyAmount] as [bbl].[billCurrencyAmount])">
<Aggregate Distinct="0" AggType="ANY">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="billCurrencyAmount" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1077" />
<ScalarOperator ScalarString="ANY([Expr1077])">
<Aggregate Distinct="0" AggType="ANY">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1077" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1078" />
<ScalarOperator ScalarString="ANY([Expr1078])">
<Aggregate Distinct="0" AggType="ANY">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1078" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1079" />
<ScalarOperator ScalarString="ANY([Expr1079])">
<Aggregate Distinct="0" AggType="ANY">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1079" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<HashKeysBuild>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="id" />
<ColumnReference Column="Expr1066" />
<ColumnReference Column="Expr1067" />
<ColumnReference Column="Expr1068" />
<ColumnReference Column="Expr1069" />
<ColumnReference Column="Expr1070" />
<ColumnReference Column="Expr1071" />
<ColumnReference Column="Expr1072" />
<ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="Item" />
<ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="InvoiceLineCategory" />
<ColumnReference Column="Expr1073" />
<ColumnReference Column="Expr1074" />
<ColumnReference Column="Expr1075" />
<ColumnReference Column="Expr1076" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[Staging].[Junifer].[BillBreakdownLine].[id] as [bbl].[id] = [Staging].[Junifer].[BillBreakdownLine].[id] as [bbl].[id] AND [Expr1066] = [Expr1066] AND [Expr1067] = [Expr1067] AND [Expr1068] = [Expr1068] AND [Expr1069] = [Expr1069] AND [Expr1070] = [Expr1070] AND [Expr1071] = [Expr1071] AND [Expr1072] = [Expr1072] AND #BillLineCategorisation.[Item] as [blc].[Item] = #BillLineCategorisation.[Item] as [blc].[Item] AND #BillLineCategorisation.[InvoiceLineCategory] as [blc].[InvoiceLineCategory] = #BillLineCategorisation.[InvoiceLineCategory] as [blc].[InvoiceLineCategory] AND [Expr1073] = [Expr1073] AND [Expr1074] = [Expr1074] AND [Expr1075] = [Expr1075] AND [Expr1076] = [Expr1076]">
<Logical Operation="AND">
... <Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Staging]" Schema="[Junifer]" Table="[BillBreakdownLine]" Alias="[bbl]" Column="id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1066" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1066" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1067" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1067" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1068" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1068" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1069" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1069" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1070" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1070" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1071" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1071" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1072" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1072" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="Item" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="Item" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="InvoiceLineCategory" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="#BillLineCategorisation" Alias="[blc]" Column="InvoiceLineCategory" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1073" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1073" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1074" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1074" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1075" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1075" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1076" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1076" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</BuildResidual>
Best Answer
It is clearly shown in the output list of your XML. Where you see the expressions you need to work back and find out what the expression definition is.
As I cannot see your full query and execution plan I will use an example to show how you can get that information.
In this case it is
PickedQuantity.Now if I extend the same query to you will see the column comparisons that you are seeing in your case.
You can see the same in XML.
As you said you have distinct in your select statement I am assuming that is why you are seeing the
Hash Aggregateoperator. You can see in the above example use ofHash Aggregatefor implementingDISTINCT.This article by Paul White gives more insight with complex examples.
This article by Craig Freedman shows example of hash aggregate being used to implement
DISTINCT.