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,
 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" 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>