主页 / dba / 问题 / 173180
Accepted
George.Palacios
Asked: 2017-05-11 01:06:42 +0800 CST

帮助解释执行计划（哈希聚合）

  • 772

所以我有一个我正在尝试优化的执行计划，并且有一段我不太理解的运算符。

计划的一部分使用了计算标量，然后是使用哈希匹配的聚合。

我有一个问题：

我如何使用哈希匹配聚合器的属性来告诉我它正在输出哪些列？我无法判断正在对哪些列执行聚合。

这是计划的一部分： 在此处输入图像描述

这是运算符属性：

在此处输入图像描述

为元素规划 XML：

<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>

在此处输入图像描述

sql-server optimization

1 个回答

  1. Best Answer

    我如何使用哈希匹配聚合器的属性来告诉我它正在输出哪些列？

    它清楚地显示在 XML 的输出列表中。在您看到表达式的位置，您需要返回并找出表达式定义是什么。

    由于我看不到您的完整查询和执行计划，我将使用一个示例来展示您如何获取该信息。

    SELECT DISTINCT [PickedQuantity]
    FROM [WideWorldImporters].[Sales].[OrderLines]

    在此处输入图像描述

    在这种情况下是PickedQuantity。在此处输入图像描述

    现在，如果我将相同的查询扩展到您，您将看到您在您的案例中看到的列比较。

    SELECT DISTINCT 
    [PickedQuantity], 
    [LastEditedBy], 
    [LastEditedWhen]
FROM 
    [WideWorldImporters].[Sales].[OrderLines]

    在此处输入图像描述

    您可以在 XML 中看到相同的内容。在此处输入图像描述

    我无法判断正在对哪些列执行聚合。

    正如您所说，您的 select 语句中有 distinct 我假设这就是您看到Hash Aggregate运算符的原因。您可以在上面的示例中看到Hash Aggregate用于实现DISTINCT.

    Paul White 的这篇文章通过复杂的示例给出了更多见解。

    Craig Freedman 的这篇文章展示了哈希聚合用于实现的示例DISTINCT。

    • 4

相关问题