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 "performance" 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.
- 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;