6

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.

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

Improve this question
6
  • 2
    @TerryKaczmarski I'm not seeing much difference between the 2016 and 2019 Execution Plans yet (btw you should update your original post with them, so other's see it more easily). One thing I noticed is the 2019 Plan actually says it went to 16 degrees of parallelism instead of 8 like on 2016's Plan. I know I recommended using the TABLOCK hint, but Erik Darling has an interesting article on how it can be worse in edge cases where your # of rows per parallelized core is less than 102,400...
    – J.D.
    Commented Dec 12, 2020 at 5:16
  • ...which it would be in your 1,000,000 row example on 2019 for 16 cores. Can you try removing the hint just in case, though I'm guessing that's not the issue here but worth double checking. Can you also post the TIME and IO stats too please?
    – J.D.
    Commented Dec 12, 2020 at 5:16
  • 2
    See related thread: learn.microsoft.com/answers/answers/184071/view.html
    – David Browne - Microsoft
    Commented Dec 12, 2020 at 17:32
  • @TerryKaczmarski Aside from my previous comment, just thinking outside the box, do you know what the previous CPU make and model was vs the new server's CPU?
    – J.D.
    Commented Dec 13, 2020 at 4:38
  • @J.D. the SQL 2016 results are from a named instance running on the same box as the SQL 2019 instance. I installed this instance when I discovered I had an issue. I wanted to compare stats/run times/etc where all the variables were the same. After this is resolved, that SQL 2016 named instance will be dropped. And BTW -- removing the with TABLOCK hint, increased the load to over an hour for 1 million row insert.
    – Terry Kaczmarski
    Commented Dec 15, 2020 at 19:10

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

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

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