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 went completely haywire, and now does a clustered index scan across Remote_Records in the query that it never had a problem with before. 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>