sql - 2 个表上的简单内连接导致错误的估计行和性能下降

Question

我无法理解为什么在 2 个表上执行内部联接的看似简单的查询会导致估计行数为 1，而实际行数几乎为 200 万。我没有看到任何缺少索引的问题，并且 98% 的查询成本都发生在索引查找上。当查询运行时，我没有看到任何 I/O 或 CPU 危险信号，这大约需要 12 秒。添加统计信息在这里似乎没有意义，因为查询只是连接 id，并且两个表都将这些 id 上的聚集索引作为主键。

此查询是遇到相同问题的较大查询的简化版本，但我将其归结为遇到相同问题的简化查询。我认为估计行和实际行之间的差异至少会导致查询性能不佳。有趣的是，用 LEFT OUTER JOIN 替换 INNER JOIN 会产生大致相同的性能，尽管它确实修复了估计行数的正确性。

查询:

SELECT StationId, Readings.Power, Readings.TimeCovered
FROM  Readings
INNER JOIN Stations ON Readings.StationId = Stations.Id

实际计划 XML:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1600.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.388499" StatementText="SELECT StationId,&#xD;&#xA;[Readings].[Power], [Readings].[TimeCovered]&#xD;&#xA;FROM  [dbo].[Readings]&#xD;&#xA;INNER JOIN [dbo].[Stations] ON [Readings].[StationId] = [Stations].[Id]" StatementType="SELECT" QueryHash="0x540DF2384788314E" QueryPlanHash="0x7546B31B38AC8153">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="3" CompileCPU="3" CompileMemory="224">
            <RelOp AvgRowSize="17" EstimateCPU="0.00118294" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.388499">
              <OutputList>
                <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Column="StationId" />
                <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Column="Power" />
                <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Column="TimeCovered" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1898419" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <NestedLoops Optimized="false">
                <OuterReferences>
                  <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Stations]" Column="Id" />
                </OuterReferences>
                <RelOp AvgRowSize="11" EstimateCPU="0.0004683" EstimateIO="0.00608796" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="283" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00655626" TableCardinality="283">
                  <OutputList>
                    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Stations]" Column="Id" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="283" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Stations]" Column="Id" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[MyDB]" Schema="[dbo]" Table="[Stations]" Index="[PK_Stations]" IndexKind="Clustered" />
                  </IndexScan>
                </RelOp>
                <RelOp AvgRowSize="17" EstimateCPU="0.0012571" EstimateIO="0.025" EstimateRebinds="0" EstimateRewinds="282" EstimateRows="1" LogicalOp="Index Seek" NodeId="2" Parallel="false" Partitioned="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.380759" TableCardinality="4246720">
                  <OutputList>
                    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Column="StationId" />
                    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Column="Power" />
                    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Column="TimeCovered" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1898419" ActualEndOfScans="283" ActualExecutions="283" />
                  </RunTimeInformation>
                  <RunTimePartitionSummary>
                    <PartitionsAccessed PartitionCount="8">
                      <PartitionRange Start="1" End="8" />
                    </PartitionsAccessed>
                  </RunTimePartitionSummary>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Column="StationId" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Column="Power" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Column="TimeCovered" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Index="[IX_Readings_StationId_SecondsSinceEpoch]" IndexKind="NonClustered" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <StartRange ScanType="GE">
                            <RangeColumns>
                              <ColumnReference Column="PtnId1000" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(1)">
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </StartRange>
                          <EndRange ScanType="LE">
                            <RangeColumns>
                              <ColumnReference Column="PtnId1000" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(8)">
                                <Const ConstValue="(8)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </EndRange>
                        </SeekKeys>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Readings]" Column="StationId" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="[MyDB].[dbo].[Stations].[Id]">
                                <Identifier>
                                  <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Stations]" Column="Id" />
                                </Identifier>
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </NestedLoops>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Answer 1

未更新的统计数据可能与此有关。尝试运行 exec sp_updatestats 并查看计划是否发生变化。还要确保在 SQL Server 上运行维护作业来定期更新统计信息。