5

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>
Improve this question
3
  • Going to a clustered index scan on the Remote_Records table. Let me change that statement a bit. And yes, when used with the rest of the function, it ends up taking minutes to run versus under a second with the old version or TOP 1.
    – Adam K.
    Nov 25, 2019 at 21:16
  • I tried to run your repro code, but after the data load ran for 10+ minutes I gave up. If you can find a faster way to load the data it might be helpful for potential answerers.
    – LowlyDBA - John M
    Nov 25, 2019 at 21:26
  • 2
    I will see what I can do to change that. It definitely takes a while on VM's (18:34 on one I tested it on). It runs in about 3 minutes on bare metal.
    – Adam K.
    Nov 25, 2019 at 21:28

2 Answers 2

Reset to default
3

Looks like this was fixed by Microsoft with one of the hotfixes to 2017. After enabling TF 4199, the execution plan switched back to what we expect.

Improve this answer
4
  • 3
    FYI, you can enable optimizer fixes at the database level as well, if you prefer a higher granularity...
    – Tibor Karaszi
    Nov 26, 2019 at 10:47
  • Thank you! I will try that as well, in case we run in isolation on another SQL Server.
    – Adam K.
    Nov 26, 2019 at 16:09
  • @AdamK. It will be nice if you elaborate about TF 4199.what was the problem and how TF 4199 help. what is TF 4199.
    – KumarHarsh
    Nov 27, 2019 at 8:18
  • @kumarharsh Trace Flag 4199. It enables the optimizer fixes that are delivered via Cumulative Updates to SQL Server.
    – Adam K.
    Nov 29, 2019 at 19:46
-1

As far as I can see, the big issue is your statistics. On the SQL2008 query, the index seek into Remote_Additional_Data from Local_Additional_Record_Data only expects one row to be returned, while under 2017, it expects more than a thousand records to be returned. This changed the previous Nested Loop join into a Merge join (which may be incorrect for the large row differential between these, but is still a possibility with more than 1000 rows to match).

So, your basic problem appears to be statistics maintenance or bad data. It doesn't look like an actual issue with the query itself.

Improve this answer
1
  • Interesting. I've tried statistics maintenance and updated stats on the tables in question, without changes to the plan. I can get it to work with OPTION(RECOMPILE) or (OPTIMIZE FOR <insert value here>), but these won't work in an in-lined table valued function. The data hasn't changed between these tables and the upgrade. We still expect a one to one relationship between Local_additional_record_data and Remote_Additional_Data, and a one to many relationship between Remote_Records and Remote_Additional_Data
    – Adam K.
    Nov 25, 2019 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.