sql-server - SQL Server 查询计划，运行时间较低，但子树成本较高

Question

为什么下面两个查询中第一个的执行时间较低，而估计的子树成本较高？

估计子树成本 只是结合其他查询性能指标(如响应时间)来衡量查询计划性能的指南，但令我惊讶的是 逆向 并排比较两个相似查询时成本和时间之间的关系。

我创建了一个示例数据库来说明这个问题，并包含了两个查询的结果 XML 查询计划。我意识到 StateID 作为外键存在于 County 和 Town 表中是一种错误的形式，但对我来说这是说明这种现象的最简单方法。

我不只是问“哪个更重要，子树成本或响应时间”。我想了解两个相似查询的查询计划子树成本和响应时间之间如何存在反比关系，以便我可以做出更明智的决策。

请注意，下面的两个查询产生完全相同的结果集。

谢谢!

CREATE TABLE dbo.County (StateID INT, CountyID INT)
CREATE TABLE dbo.Town (StateID INT, CountyID INT, TownID INT)

DECLARE @State TABLE (StateID INT, PRIMARY KEY CLUSTERED (StateID))
DECLARE @County TABLE (CountyID INT, PRIMARY KEY CLUSTERED (CountyID))

INSERT INTO @State
VALUES (27)

INSERT INTO @County
SELECT DISTINCT
    c.CountyID
FROM dbo.County c
JOIN @State s ON c.StateID = s.StateID

-- low time, high cost query
SELECT DISTINCT
    t.CountyID,
    t.TownID
FROM dbo.Town t
JOIN @State s
    ON t.StateID = s.StateID

-- low cost, high time query
SELECT DISTINCT
    t.CountyID,
    t.TownID
FROM dbo.Town t
JOIN @State s
    ON t.StateID = s.StateID
JOIN @County c
    ON t.CountyID = c.CountyID

以下查询计划适用于估计子树成本为 6.06 且耗时为 1114 毫秒的查询。

<StmtSimple StatementCompId="6" StatementEstRows="273495" StatementId="3" StatementOptmLevel="FULL" StatementSubTreeCost="6.06304" StatementText="SELECT DISTINCT&#xD;&#xA;&#x9;t.CountyID,&#xD;&#xA;&#x9;t.TownID&#xD;&#xA;FROM dbo.Town t&#xD;&#xA;JOIN @State s&#xD;&#xA;&#x9;ON t.StateID = s.StateID&#xD;&#xA;&#xD;&#xA;-- low cost, high time query&#xD;" StatementType="SELECT" QueryHash="0x9347C19165C31DBF" QueryPlanHash="0x98EE57C66D8B347A">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="2" MemoryGrant="28144" CachedPlanSize="32" CompileTime="8" CompileCPU="8" CompileMemory="312">
            <RelOp AvgRowSize="15" EstimateCPU="0.388488" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="273495" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="6.06304">
              <OutputList>
                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="13027" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Parallelism>
                <RelOp AvgRowSize="15" EstimateCPU="3.79725" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="273495" LogicalOp="Aggregate" NodeId="1" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="5.67455">
                  <OutputList>
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                  </OutputList>
                  <MemoryFractions Input="0.998723" Output="1" />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="2" ActualRows="6515" ActualEndOfScans="1" ActualExecutions="1" />
                    <RunTimeCountersPerThread Thread="1" ActualRows="6512" ActualEndOfScans="1" ActualExecutions="1" />
                    <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                  </RunTimeInformation>
                  <Hash>
                    <DefinedTowns />
                    <HashKeysBuild>
                      <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                      <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                    </HashKeysBuild>
                    <BuildResidual>
                      <ScalarOperator ScalarString="[Locality].[dbo].[Town].[CountyID] as [t].[CountyID] = [Locality].[dbo].[Town].[CountyID] as [t].[CountyID] AND [Locality].[dbo].[Town].[TownID] as [t].[TownID] = [Locality].[dbo].[Town].[TownID] as [t].[TownID]">
                        <Logical Operation="AND">
                          <ScalarOperator>
                            <Compare CompareOp="IS">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Compare CompareOp="IS">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Logical>
                      </ScalarOperator>
                    </BuildResidual>
                    <RelOp AvgRowSize="15" EstimateCPU="0.494228" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="273495" LogicalOp="Repartition Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.8773">
                      <OutputList>
                        <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                        <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="2" ActualRows="1017328" ActualEndOfScans="1" ActualExecutions="1" />
                        <RunTimeCountersPerThread Thread="1" ActualRows="1093191" ActualEndOfScans="1" ActualExecutions="1" />
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                      </RunTimeInformation>
                      <Parallelism PartitioningType="Hash">
                        <PartitionColumns>
                          <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                          <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                        </PartitionColumns>
                        <RelOp AvgRowSize="15" EstimateCPU="0.571604" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="273495" LogicalOp="Inner Join" NodeId="3" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.38307">
                          <OutputList>
                            <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                            <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                            <RunTimeCountersPerThread Thread="1" ActualRows="2110519" ActualEndOfScans="1" ActualExecutions="1" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <NestedLoops Optimized="true">
                            <OuterReferences>
                              <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                            </OuterReferences>
                            <RelOp AvgRowSize="11" EstimateCPU="0.0285019" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distribute Streams" NodeId="5" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.031785">
                              <OutputList>
                                <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
                                <RunTimeCountersPerThread Thread="1" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                              </RunTimeInformation>
                              <Parallelism PartitioningType="RoundRobin">
                                <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                                  <OutputList>
                                    <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="1" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                                    <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                                  </RunTimeInformation>
                                  <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                    <DefinedTowns>
                                      <DefinedTown>
                                        <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                                      </DefinedTown>
                                    </DefinedTowns>
                                    <Object Table="[@State]" Index="[PK__#3AB788A__C3BA3B5A3C9FD11A]" Alias="[s]" />
                                  </IndexScan>
                                </RelOp>
                              </Parallelism>
                            </RelOp>
                            <RelOp AvgRowSize="15" EstimateCPU="0.301001" EstimateIO="0.478681" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="273495" LogicalOp="Index Seek" NodeId="7" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.779682" TableCardinality="6291320">
                              <OutputList>
                                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="2" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                                <RunTimeCountersPerThread Thread="1" ActualRows="2110519" ActualEndOfScans="1" ActualExecutions="1" />
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                <DefinedTowns>
                                  <DefinedTown>
                                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                                  </DefinedTown>
                                  <DefinedTown>
                                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                                  </DefinedTown>
                                </DefinedTowns>
                                <Object Database="[Locality]" Schema="[dbo]" Table="[Town]" Index="[NCX_Town_StateID_CountyID_TownID]" Alias="[t]" IndexKind="NonClustered" />
                                <SeekPredicates>
                                  <SeekPredicateNew>
                                    <SeekKeys>
                                      <Prefix ScanType="EQ">
                                        <RangeColumns>
                                          <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="StateID" />
                                        </RangeColumns>
                                        <RangeExpressions>
                                          <ScalarOperator ScalarString="@State.[StateID] as [s].[StateID]">
                                            <Identifier>
                                              <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </RangeExpressions>
                                      </Prefix>
                                    </SeekKeys>
                                  </SeekPredicateNew>
                                </SeekPredicates>
                              </IndexScan>
                            </RelOp>
                          </NestedLoops>
                        </RelOp>
                      </Parallelism>
                    </RelOp>
                  </Hash>
                </RelOp>
              </Parallelism>
            </RelOp>
          </QueryPlan>
        </StmtSimple>

以下查询计划适用于估计子树成本为 0.14 且耗时为 4614 毫秒的查询。

<StmtSimple StatementCompId="7" StatementEstRows="339.324" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.142839" StatementText="SELECT DISTINCT&#xD;&#xA;&#x9;t.CountyID,&#xD;&#xA;&#x9;t.TownID&#xD;&#xA;FROM dbo.Town t&#xD;&#xA;JOIN @State s&#xD;&#xA;&#x9;ON t.StateID = s.StateID&#xD;&#xA;JOIN @County c&#xD;&#xA;&#x9;ON t.CountyID = c.CountyID&#xD;&#xA;&#xD;" StatementType="SELECT" QueryHash="0xF7B13FA8059B141C" QueryPlanHash="0x31CEE9DAF12E77A5">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" MemoryGrant="1584" CachedPlanSize="40" CompileTime="5" CompileCPU="5" CompileMemory="376">
            <RelOp AvgRowSize="15" EstimateCPU="0.00454996" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="339.324" LogicalOp="Distinct Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.142839">
              <OutputList>
                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
              </OutputList>
              <MemoryFractions Input="1" Output="1" />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="13027" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Sort Distinct="true">
                <OrderBy>
                  <OrderByColumn Ascending="true">
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                  </OrderByColumn>
                  <OrderByColumn Ascending="true">
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                  </OrderByColumn>
                </OrderBy>
                <RelOp AvgRowSize="15" EstimateCPU="0.0571741" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="339.324" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.127028">
                  <OutputList>
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                    <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                  </OutputList>
                  <MemoryFractions Input="0" Output="0" />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="2110519" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Hash>
                    <DefinedTowns />
                    <HashKeysBuild>
                      <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                    </HashKeysBuild>
                    <HashKeysProbe>
                      <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="StateID" />
                    </HashKeysProbe>
                    <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                      <OutputList>
                        <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                        <DefinedTowns>
                          <DefinedTown>
                            <ColumnReference Table="@State" Alias="[s]" Column="StateID" />
                          </DefinedTown>
                        </DefinedTowns>
                        <Object Table="[@State]" Index="[PK__#3AB788A__C3BA3B5A3C9FD11A]" Alias="[s]" />
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="19" EstimateCPU="0.0357739" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8558.34" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.066568">
                      <OutputList>
                        <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="StateID" />
                        <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                        <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="2110519" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <NestedLoops Optimized="false">
                        <OuterReferences>
                          <ColumnReference Table="@County" Alias="[c]" Column="CountyID" />
                        </OuterReferences>
                        <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                          <OutputList>
                            <ColumnReference Table="@County" Alias="[c]" Column="CountyID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="137" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                            <DefinedTowns>
                              <DefinedTown>
                                <ColumnReference Table="@County" Alias="[c]" Column="CountyID" />
                              </DefinedTown>
                            </DefinedTowns>
                            <Object Table="[@County]" Index="[PK__#3E88198__B68F9DF7407061FE]" Alias="[c]" />
                          </IndexScan>
                        </RelOp>
                        <RelOp AvgRowSize="19" EstimateCPU="0.00957118" EstimateIO="0.0179398" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8558.34" LogicalOp="Index Seek" NodeId="5" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.027511" TableCardinality="6291320">
                          <OutputList>
                            <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="StateID" />
                            <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                            <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="2110519" ActualEndOfScans="137" ActualExecutions="137" />
                          </RunTimeInformation>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                            <DefinedTowns>
                              <DefinedTown>
                                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="StateID" />
                              </DefinedTown>
                              <DefinedTown>
                                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                              </DefinedTown>
                              <DefinedTown>
                                <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="TownID" />
                              </DefinedTown>
                            </DefinedTowns>
                            <Object Database="[Locality]" Schema="[dbo]" Table="[Town]" Index="[NCX_Town_CountyID_inc_StateID_TownID]" Alias="[t]" IndexKind="NonClustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[Locality]" Schema="[dbo]" Table="[Town]" Alias="[t]" Column="CountyID" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="@County.[CountyID] as [c].[CountyID]">
                                        <Identifier>
                                          <ColumnReference Table="@County" Alias="[c]" Column="CountyID" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                  </Hash>
                </RelOp>
              </Sort>
            </RelOp>
          </QueryPlan>
        </StmtSimple>

Answer 1

虽然我没有深入研究您的查询计划，但我有一些建议和建议的解释。建议很简单:不要对行数超过两个数量级(100 行)的表使用表变量。可能的解释是它们的性能可能非常差，生成的查询计划可能不准确或效率低下，per Microsoft's article on table data types :

Best practices

Do not use table variables to store large amounts of data (more than 100 rows). Plan choices may not be optimal or stable when a table variable contains a large amount of data. Consider rewriting such queries to use temporary tables or use the USE PLAN query hint to ensure the optimizer uses an existing query plan that works well for your scenario.

和:

Limitations and Restrictions

table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.

Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Queries that read table variables without modifying them can still be parallelized.

...

在实践中，我发现使用表变量会妨碍并行查询计划的生成，并且会损害除最简单、最不复杂的表变量以外的所有表变量的性能。

相反，我发现几乎在所有情况下临时表的性能都更高，只要您小心及时清理自己(或使用短期 session 以允许服务器清除临时表) 然后你会得到更好的性能。

至于为什么第二个查询计划成本较低但执行时间较长？很可能有很多很多县(1000s？10000s？)并且正在生成的查询计划假设有少量(100s)。查询优化器认为@County 可能少于 100 行，因此它可能认为结果集将非常具体，因为城镇列表与 <100 个条目的一个列表连接，然后与第二个 <100 条目的列表连接，很可能少于 100 行。

实际上，每个城镇都有一个县，因此连接不是很有选择性，并且使用的连接类型选择不佳。在每种情况下，嵌套循环都用于最后一个连接，但嵌套循环连接对于大型排序数据来说效率极低。这可能是问题的症结所在。

使用临时表而不是表变量重新运行您的查询，我猜您会得到具有非常不同的性能特征的非常不同的查询计划。