SQL Server 2019 Performance issue with large INSERT We are converting several ETL processes from SQL Server 2016, Windows 2012 server to a Windows 2019, SQL Server 2019 (CU8) environment.
One process is running longer in SQL Server 2019 than it used to in SQL Server 2016. The process performs an INSERT into B, select x from A, where x is a series of substring and case statements.
Table A basically contains 1 data column, 6,000 bytes long. Table B consists of over 950 data columns.  I bulk insert the data from a flat file into table A, then I parse the data into table B using various substring commands.
Table A contains 4.7 million rows and loads in 47 minutes, I split out 1 million rows for testing purposes and this loads in 10 minutes.
On my SQL Server 2016, Windows 2012 server, a full load runs in 12 - 14 minutes, while the 1 million sampling runs in 1 minute 51 seconds.
For grins, I stalled SQL Server 2016 on my ETL 2019 server and ran the same tests. Full load 7 minutes 14 seconds, whereas the 1 million sampling took 1 minute 32 seconds.
All three instances have a MAXDOP of 8 and a Cost Threshold For Parallelism (CTFP) of 50.
This is a dedicated server running SQL Server 2019 for ETL processing. No other apps running on it:

2 - 3.80Ghz processors with 4 cores per socket and 8 logical processors per socket
1571465 MB of RAM (1.5 TeraBytes)
16,813 GB on-board SSD storage
Very controlled testing, no other processes running at the time of tests

While the actual ETL process is procedure based, I pulled the insert code out and I'm running it in SSMS on the local server as T-SQL commands, for testing.
I understand there are a lot of new &quot;performance&quot; improvements in SQL Server 2019 and I've tried a lot of different combinations (different combinations of MAXDOP and CTFP, turning different SCOPE values on and off), so far, nothing has helped reduce the run times.
Some observations:

reducing the target table to only 100 columns, 1 million rows loaded in 1 minute; 4.7 million loaded in 5 minutes
increasing the column count to 250 and loading 1 million rows took 6 minutes.
I cannot put my finger on it, but something around the substring/case function and how its interacting with SQL Server is causing me some grief.
Multiple executions with different MaxDOP (0,4,8) and CTFP, resulted in no difference, except MaxDop=4, which increased run time.

Snippet of code:
INSERT into Base with (TABLOCK)
SELECT 
   CHD_A = substring(importtext,14,28)
  ,CHD_B = getdate()
  ,CHD_C = substring(importtext,14,4)
  ,CHD_D = substring(importtext,18,4)
  ,CHD_E = substring(importtext,22,4)
  ,CHD_F = CASE WHEN substring(importtext,45,6) = ' ' THEN NULL ELSE RTRIM(substring(importtext,45,6)) END  
 , and so on and so on for 950+ columns.

P.S. Ran a quick test of this same load on a VM server running SQL Server 2017, with 16GB of ram. Query finished in 1 minute 19 seconds, for 1 million rows.
So, I'm reaching out to SQL Server 2019 guru's for advise and where to look, what to tune. Converting to SSIS at this time is not an option.
Here are the plans;
SQL 2016
SQL 2017
SQL 2019
From comments:
To all intents and purposes, those plans are identical. As such, I'd focus more on differences in the physical set up and server settings. If the query optimizer for 2016 and 2019 are arriving at identical plans, but, behavior is different, chasing optimizer settings, query hints, statistics, cardinality estimation and other plan driven decisions is a waste of time. Since the majority of the performance difference is in that clustered index scan (1 sec vs. 28 seconds) it's likely to be a disk or memory problem. Although the actual insert operation is also twice as long. Same likely issue. - Grant Fritchey