Simple Inner Join on 2 tables resulting in wrong estimated rows and slow performance

Question

I'm having trouble understanding why a seemingly simple query which does an inner join on 2 tables is resulting in an estimated row count of 1, when the actual is almost 2 million. I don't see any issues with missing indexes, and 98% of the cost of query is happening as an index seek. I don't see any I/O or CPU red flags when they query runs, which takes about 12 seconds. Adding statistics doesn't seem to make sense here since the query is simply joining on ids, and both tables have clustered indexes on those ids as primary keys.

This query is a simplified version of a larger query experiencing the same issues, but I boiled it down to this simplified query which experiences the same problem. I assume the discrepancy between estimated and actual rows is at least contributing to sub-optimal query performance. Interestingly, substituting a LEFT OUTER JOIN for the INNER JOIN leads to approximately the same performance, even though it does fix the Estimated Rows to be correct.

Query:

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

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

Non updated statistics might have something to do with it. Try running a exec sp_updatestats and see if the plan changes. Also make sure you have a maintenance job running on the SQL server that updates the statistics periodically.