0

We've upgraded a SQL Server to 2019 from 2014.

In 2014 this query was fine:

INSERT INTO [schx].[US_New]
SELECT *
FROM [schx].[US_New_Stg]
WHERE FormattedID NOT IN (SELECT FormattedID
                          FROM [schx].[DLT]
                          WHERE [Type] = 'Hierarchical Root'
                         );

Upon upgrade (no code changes only SQL Server Upgrade), the query killed the process to which it belonged, instead of running for 4 minutes now it went on for 8 hours, and then it would time out.

We ended up adding this: OPTION (USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' )) to the end of the query, and then things were fine again.

2 questions:

  1. Because we have to use the Legacy CE, does this mean that the New CE one isn't as robust as the Legacy CE?
  2. If instead of a subquery in the WHERE clause, we left-joined both and selected NOT NULL, would that remove the need for using the Legacy CE?

Thanks!

A.G.
  • 2,089
  • 3
  • 30
  • 52
  • 1
    stats up to date? pls post query plans – Mitch Wheat Jan 20 '22 at 05:31
  • Thanks for your response. Unfortunately, 2 of the tables used here are only temporary (not TEMP tables) and they are removed when the processing is complete. So cannot quickly get the plan. Might have to set a Profiler on the next run and see if we can get it. – A.G. Jan 20 '22 at 14:57

0 Answers0