Sql-server – Query Execution oddities between SQL Server 2008r2, 2017 and 2019

I have a very basic query that was running completely fine with index seeks across the board on SQL Server 2008r2. When we migrated to SQL Server 2017, it started performing worse, and now does a clustered index scan across Remote_Records in the query that it never had a problem with before, thus causing the rest of our query to run for a minute or longer dependent on how many records the function is applied against. This ran in mere seconds before we upgraded. Switching it back to use the legacy Cardinality Estimator changed the plan back, but that's an option that we would like to avoid.

I also tested it against SQL Server 2019, and it returned to the same type of plan as SQL 2008r2, except the clustered index seek on Local_Record_Additional_Data is now using Batch Mode on RowStore processing to get the seek.

I can get the query to not use a clustered index scan on Remote_Records by using a TOP 1 on the query in SQL 2017, but since this worked in SQL 2008r2 without any additional modifications, I'm sort of at a loss why the Cardinality Estimator is off in this case.

This query is part of a larger in-lined table valued function that is applied against a given id value to gather certain data that we want to display. This is the only part of the function that is having issues.

Any advice would be appreciated.

Here are the execution plan links:
Original SQL 2008r2 Plan: https://www.brentozar.com/pastetheplan/?id=S1G4pnK2H

Original SQL 2017 Plan: https://www.brentozar.com/pastetheplan/?id=B1ALKXQ2S

SQL 2017 with TF 9481: https://www.brentozar.com/pastetheplan/?id=ByguM2Y2S

Microsoft has not published the DTD for SQL 2019, I have included the XML below for the SQL 2019 plan.

Code to generate the tables and data set to mirror what we're seeing in production:

CREATE TABLE Remote_Records  
(
    Remote_record_id int identity (1,1) PRIMARY KEY CLUSTERED,
    Bit_1 bit,
    Bit_2 bit
)
GO
CREATE TABLE Local_Record_Additional_Data 
(
    Local_record_id int identity(1,1) PRIMARY KEY CLUSTERED,
    Status_data varchar(100),
    Device_data varchar(25)
)
GO
CREATE TABLE Remote_Additional_Data 
(
    Remote_add_data_id int identity(1,1) PRIMARY KEY CLUSTERED,
    Remote_record_id int NOT NULL CONSTRAINT FK_Remote_Additional_Data_Remote_Records_Remote_record_id  FOREIGN KEY (Remote_record_id) REFERENCES Remote_Records(Remote_record_id),
    Local_record_add_id int NULL CONSTRAINT FK_Remote_Additional_Data_Local_Record_Additional_Data FOREIGN KEY (Local_record_add_id) REFERENCES Local_Record_Additional_Data(Local_record_id)
)
GO



DECLARE @count int = 1
WHILE @count <=319756
BEGIN
    INSERT INTO Remote_Records
    SELECT 0,0
    SET @count = @count + 1
END
GO

DECLARE @count int = 1
WHILE @count <=457
BEGIN
    INSERT INTO Local_Record_Additional_Data(Status_data,Device_data)
    SELECT NULL,NULL
    SET @count = @count + 1
END
GO


DECLARE @count int = 2
DECLARE @count_2 int = 1
DECLARE @rr_id int
DECLARE @max_rr_id int 
DECLARE @rad_id int = 1
DECLARE @lrad_id int
SELECT @max_rr_id = max(Remote_record_id) FROM Remote_Records
WHILE @count <= 486004
BEGIN
    IF @count > @max_rr_id
    BEGIN
        SET @count_2 = @count_2 + 1
    END

    IF @count >= 486004-456
    BEGIN
        SET @lrad_id = ISNULL(@lrad_id,0) + 1
    END
    SELECT @rr_id = CASE WHEN @count > @max_rr_id THEN @count_2 ELSE @count END

    INSERT INTO Remote_Additional_Data(Remote_record_id, Local_record_add_id)
    SELECT @rr_id, @lrad_id

    SET @count = @count + 1

END
GO
sp_createstats 'NO','NO','NORECOMPUTE'
GO

UPDATE STATISTICS Remote_Records WITH FULLSCAN
GO
UPDATE STATISTICS Local_Record_Additional_Data WITH FULLSCAN
GO
UPDATE STATISTICS Remote_Additional_Data WITH FULLSCAN
GO
CREATE INDEX IX_Remote_Additional_Data_Remote_record_id ON [Remote_Additional_Data](Remote_record_id) INCLUDE (Local_record_add_id) 
GO
CREATE INDEX IX_Remote_Additional_Data_Local_record_add_id ON [Remote_Additional_Data](Local_record_add_id) INCLUDE (Remote_record_id) 
GO

Here is the problem query:

declare @lra_id int = 267
SELECT  Local_record_add_id,
        Bit_1,
        Bit_2
        FROM Remote_records se 
        INNER JOIN Remote_Additional_Data foe ON se.Remote_record_id = foe.Remote_record_id
        INNER JOIN Local_Record_Additional_Data foh on foe.Local_record_add_id = foh.Local_record_id
        WHERE foe.Local_record_add_id = @lra_id
go

The SQL 2019 Execution 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.539" Build="15.0.2000.5" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.00985766" StatementText="SELECT  Local_record_add_id,&#xD;&#xA;      Bit_1,&#xD;&#xA;        Bit_2&#xD;&#xA;     FROM Remote_records se &#xD;&#xA;       INNER JOIN Remote_Additional_Data foe ON se.Remote_record_id = foe.Remote_record_id&#xD;&#xA;       INNER JOIN Local_Record_Additional_Data foh on foe.Local_record_add_id = foh.Local_record_id&#xD;&#xA;      WHERE foe.Local_record_add_id = @lra_id" StatementType="SELECT" QueryHash="0x6825DB46BFFA067E" QueryPlanHash="0xE4DD43257F5DD402" RetrievedFromCache="false" SecurityPolicyApplied="false" BatchModeOnRowStoreUsed="true">
          <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" CachedPlanSize="32" CompileTime="8" CompileCPU="8" CompileMemory="376">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="102400" EstimatedPagesCached="153600" EstimatedAvailableDegreeOfParallelism="12" MaxCompileMemory="18406536" />
            <OptimizerStatsUsage>
              <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[Local_record_add_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:16.28" />
              <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[IX_Remote_Additional_Data_Remote_record_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:16.5" />
              <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[Remote_record_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:15.86" />
              <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[IX_Remote_Additional_Data_Local_record_add_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:17.78" />
              <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Statistics="[PK__Remote_R__FEF044CF617642DF]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:14.75" />
            </OptimizerStatsUsage>
            <QueryTimeStats CpuTime="0" ElapsedTime="0" />
            <RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985766">
              <OutputList>
                <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
              </RunTimeInformation>
              <NestedLoops Optimized="false">
                <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="457">
                  <OutputList />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="1" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                  </RunTimeInformation>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues />
                    <Object Database="[Stats_Testing]" Schema="[dbo]" Table="[Local_Record_Additional_Data]" Index="[PK__Local_Re__E377E55CA10BE177]" Alias="[foh]" IndexKind="Clustered" Storage="RowStore" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Local_Record_Additional_Data]" Alias="[foh]" Column="Local_record_id" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="[@lra_id]">
                                <Identifier>
                                  <ColumnReference Column="@lra_id" />
                                </Identifier>
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
                <RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
                  <OutputList>
                    <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                    <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                    <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="false">
                    <OuterReferences>
                      <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                    </OuterReferences>
                    <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="486003">
                      <OutputList>
                        <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                        <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Index="[IX_Remote_Additional_Data_Local_record_add_id]" Alias="[foe]" IndexKind="NonClustered" Storage="RowStore" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[@lra_id]">
                                    <Identifier>
                                      <ColumnReference Column="@lra_id" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="319756">
                      <OutputList>
                        <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                        <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Index="[PK__Remote_R__FEF044CF617642DF]" Alias="[se]" IndexKind="Clustered" Storage="RowStore" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Remote_record_id" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[Stats_Testing].[dbo].[Remote_Additional_Data].[Remote_record_id] as [foe].[Remote_record_id]">
                                    <Identifier>
                                      <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </NestedLoops>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@lra_id" ParameterDataType="int" ParameterRuntimeValue="(267)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>