SQL2016 Upgrade to SQL2019 Long Running Query

Terry Kaczmarski 1

Hello Everyone

We are converting several ETL processes from a windows 2012, SQL2016 environment to a windows 2019, SQL2019 environment. All is going well, except for one process. This process is running longer in SQL2019 then SQL2016.

The process performs an INSERT into A, select x from B, where x is a series of substring and case statements.

The source table basically contains 1 data field, 6,000 bytes long. The target table contains over 950 data columns, the largest in our ETL processes.

The source table contains 4.7 million rows and loads in 47 minutes, 100,000 rows per minute.

I split out 1 million rows for testing purposes, this runs in 10 minutes. Again, 100,000 rows per minute.

On my SQL2016, windows 2012 server, a full load runs in 12 - 14 minutes, while the 1 million sampling runs in 2 minutes.

For grins, I stalled SQL2016 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.

While the actual ETL process is procedure based, I pulled the insert code out and I'm running it in SSMS as t-sql commands.

I understand there are a lot of new "performance" improvements in SQL2019 and I've tried a lot of different combinations, so far, nothing has helped reduce the run times.

Some observations: A) reducing the target table to only 100 columns, 1 million rows loaded in 1 minute; 4.7 million loaded in 5 minutes. B) 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 is causing me some grief.

So, I'm reaching out to SQL2019 guru's for advise and where to look, what to tune. Converting to SSMS at this time is not an option.

Your thoughts?

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-11-23T22:34:39.547+00:00

What compatibility level are you using on SQL 2019? 150 or something lower? What if you lower the compat level to 130? I'm not saying that this is the solution, but this to serves narrow down what to look for.

What do you mean with "converting to SSMS"?

You did apply CU8 on the SQL 2019 instance, didn't you?

7 answers

Terry Kaczmarski 1 Reputation point

2020-12-02T20:47:47.737+00:00

New query execution plans are attached.44428-sql2016-xmlexecplan-20201202.txt

44450-sql2019-xmlexecplan-20201202.txt
Please sign in to rate this answer.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-12-02T22:30:24.26+00:00

Some observations: For the table scan, there is not that much difference in CPU time, but a bigger difference in actual time.

For the INSERT operator there is a big difference both in CPU time and actual time.

I note that on SQL 2016 there are 8 threads, whereas as there are 16 on SQL 2019. Not that i think it matters, but what happens with OPTION (MAXDOP 8)?

The SQL 2019 plan also has waitstats - and again SOS_SCHEDULER_YIELD is on the top.

Did you check the collations?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

David Browne 111 Microsoft Employee

I see no significant difference between SQL Server 2016 and 2019 with the following test script. Can you try, and perhaps modify it to reproduce the behavior you are seeing?

use tempdb  
select @@version   
set nocount on  
go  
  
drop table if exists stg   
drop table if exists foo  
  
create table stg(id int identity primary key, importtext nvarchar(max))  
insert into stg(importtext)   
select replicate (cast('1' as nvarchar(max)), 500)  
from sys.messages   
  
go  
  
    SELECT   
       CHD_FullAccountNumber    =   substring(importtext,14,28)  
      ,CHD_DateInserted         =   getdate()  
      ,CHD_ProcessDate          =   getdate()--CONVERT(varchar(26),@HeaderDateTime)  
      ,CHD_System               =   substring(importtext,14,4)  
      ,CHD_Principal            =   substring(importtext,18,4)  
      ,CHD_Agent                =   substring(importtext,22,4)  
      ,CHD_AccountNumber        =   substring(importtext,26,16)  
      ,CHD_NEW_FILE_TYPE        =   substring(importtext,42,1)  
      ,CHD_RECORD_FORMAT        =   substring(importtext,43,2)  
      ,CHD_CUSTOMER_IDENT       =   CASE   
                                   WHEN substring(importtext,45,6) = ' ' THEN NULL  
                                      ELSE RTRIM(substring(importtext,45,6))  
                                    END    
      ,CHD_CLIENT_NUMBER        =   substring(importtext,51,4)  
      ,CHD_ACCOUNT_NO_EXPN      =   substring(importtext,55,3)  
      ,CHD_CIT_TOTL_CT          =   substring(importtext,58,5)  
      ,CHD_NO_DEL_ITEMS         =   substring(importtext,63,5)  
      ,CHD_NO_BEHAVIOR_SEGS     =   substring(importtext,68,5)  
      ,CHD_NO_CUSTOMER_SEGS     =   substring(importtext,73,5)  
      ,CHD_NO_HRSK_ACS_SEGS     =   substring(importtext,78,5)  
      ,CHD_FUTURE_SMALL_SEGS    =   CASE   
                                   WHEN substring(importtext,83,6) = ' ' THEN NULL  
                                      ELSE RTRIM(substring(importtext,83,6))  
                                    END  
      ,CHD_NO_SMALL_SEGS        =   substring(importtext,89,5)  
      ,CHD_NO_ALP_STAG_SEGS     =   substring(importtext,94,5)  
      ,CHD_NO_NEW_MISC2_SEGS    =   substring(importtext,99,7)  
      ,CHD_NO_NEW_MISC3_SEGS    =   substring(importtext,106,5)  
      ,CHD_NO_SLS_SYD_SEGS      =   substring(importtext,111,5)  
      ,CHD_FUTURE_LARGE_SEGS    =   substring(importtext,116,4)  
      ,CHD_NO_LARGE_SEGS        =   substring(importtext,120,5)  
      ,CHD_NO_SUPER_LARGE_SEGS  =   substring(importtext,125,5)  
      ,CHD_NO_FLAP_SEGS         =   substring(importtext,130,9)  
      ,CHD_NO_CHECKPOINTS       =   substring(importtext,139,9)  
      ,CHD_NO_TLP_BONUS_SEGS    =   substring(importtext,148,9)  
      ,CHD_NO_GLOBAL_NAME_CT    =   substring(importtext,157,5)  
      ,CHD_NO_GLOBAL_ADDRESS_CT =   substring(importtext,162,5)  
      ,CHD_NO_GLOBAL_PHONE_CT   =   substring(importtext,167,5)  
      ,CHD_NO_MMB_ITEM_CT       =   substring(importtext,172,5)  
      ,CHD_NO_PYST_ITEM_CT      =   substring(importtext,177,5)  
      ,CHD_NMBR_DSPT_ITEM_CT    =   substring(importtext,182,5)  
      ,CHD_AFK_ENTITY           =   substring(importtext,187,4)  
      ,CHD_AFK_CARD_NOCARD      =   substring(importtext,191,4)  
      ,CHD_AFK_BRANCH_PORTFO    =   substring(importtext,195,6)  
      ,CHD_AFK_OTHER1           =   substring(importtext,201,6)  
      ,CHD_EXTERNAL_STATUS      =   substring(importtext,207,1)  
      ,CHD_INTERNAL_STATUS      =   substring(importtext,208,1)  
      ,CHD_PREV_EXT_STATUS      =   substring(importtext,209,1)  
      ,CHD_CYCLE_CODE_99        =   substring(importtext,210,2)  
      ,CHD_DATE_LAST_NONMON     =  CASE WHEN substring(importtext,214,6) = 000000 THEN NULL  
                                        ELSE substring(importtext,214,6)          END  
      ,CHD_PREVIOUS_CYCLE_CODE_99   =   substring(importtext,220,2)  
      ,CHD_DLVR_PONT_NR         =   substring(importtext,222,9)  
      ,CHD_DLVR_BAR1_NR         =   substring(importtext,231,19)  
      ,CHD_DLVR_BAR2_NR         =   substring(importtext,250,19)  
      ,CHD_CHARGE_DDA_CODE      =   substring(importtext,269,1)  
      ,CHD_TRANSIT_ROUTING_NO   =   substring(importtext,270,10)  
      ,CHD_CHECKING_ACCT_NO     =   CASE   
                                   WHEN substring(importtext,280,17) = ' '     THEN NULL  
                                      ELSE RTRIM(substring(importtext,280,17))    END  
  into foo  
  from stg   
  where 1=0  
  
go  
set statistics time on  
go  
  
    insert into foo with (tablock)  
    SELECT   
       CHD_FullAccountNumber    =   substring(importtext,14,28)  
      ,CHD_DateInserted         =   getdate()  
      ,CHD_ProcessDate          =   getdate()--CONVERT(varchar(26),@HeaderDateTime)  
      ,CHD_System               =   substring(importtext,14,4)  
      ,CHD_Principal            =   substring(importtext,18,4)  
      ,CHD_Agent                =   substring(importtext,22,4)  
      ,CHD_AccountNumber        =   substring(importtext,26,16)  
      ,CHD_NEW_FILE_TYPE        =   substring(importtext,42,1)  
      ,CHD_RECORD_FORMAT        =   substring(importtext,43,2)  
      ,CHD_CUSTOMER_IDENT       =   CASE   
                                   WHEN substring(importtext,45,6) = ' ' THEN NULL  
                                      ELSE RTRIM(substring(importtext,45,6))  
                                    END    
      ,CHD_CLIENT_NUMBER        =   substring(importtext,51,4)  
      ,CHD_ACCOUNT_NO_EXPN      =   substring(importtext,55,3)  
      ,CHD_CIT_TOTL_CT          =   substring(importtext,58,5)  
      ,CHD_NO_DEL_ITEMS         =   substring(importtext,63,5)  
      ,CHD_NO_BEHAVIOR_SEGS     =   substring(importtext,68,5)  
      ,CHD_NO_CUSTOMER_SEGS     =   substring(importtext,73,5)  
      ,CHD_NO_HRSK_ACS_SEGS     =   substring(importtext,78,5)  
      ,CHD_FUTURE_SMALL_SEGS    =   CASE   
                                   WHEN substring(importtext,83,6) = ' ' THEN NULL  
                                      ELSE RTRIM(substring(importtext,83,6))  
                                    END  
      ,CHD_NO_SMALL_SEGS        =   substring(importtext,89,5)  
      ,CHD_NO_ALP_STAG_SEGS     =   substring(importtext,94,5)  
      ,CHD_NO_NEW_MISC2_SEGS    =   substring(importtext,99,7)  
      ,CHD_NO_NEW_MISC3_SEGS    =   substring(importtext,106,5)  
      ,CHD_NO_SLS_SYD_SEGS      =   substring(importtext,111,5)  
      ,CHD_FUTURE_LARGE_SEGS    =   substring(importtext,116,4)  
      ,CHD_NO_LARGE_SEGS        =   substring(importtext,120,5)  
      ,CHD_NO_SUPER_LARGE_SEGS  =   substring(importtext,125,5)  
      ,CHD_NO_FLAP_SEGS         =   substring(importtext,130,9)  
      ,CHD_NO_CHECKPOINTS       =   substring(importtext,139,9)  
      ,CHD_NO_TLP_BONUS_SEGS    =   substring(importtext,148,9)  
      ,CHD_NO_GLOBAL_NAME_CT    =   substring(importtext,157,5)  
      ,CHD_NO_GLOBAL_ADDRESS_CT =   substring(importtext,162,5)  
      ,CHD_NO_GLOBAL_PHONE_CT   =   substring(importtext,167,5)  
      ,CHD_NO_MMB_ITEM_CT       =   substring(importtext,172,5)  
      ,CHD_NO_PYST_ITEM_CT      =   substring(importtext,177,5)  
      ,CHD_NMBR_DSPT_ITEM_CT    =   substring(importtext,182,5)  
      ,CHD_AFK_ENTITY           =   substring(importtext,187,4)  
      ,CHD_AFK_CARD_NOCARD      =   substring(importtext,191,4)  
      ,CHD_AFK_BRANCH_PORTFO    =   substring(importtext,195,6)  
      ,CHD_AFK_OTHER1           =   substring(importtext,201,6)  
      ,CHD_EXTERNAL_STATUS      =   substring(importtext,207,1)  
      ,CHD_INTERNAL_STATUS      =   substring(importtext,208,1)  
      ,CHD_PREV_EXT_STATUS      =   substring(importtext,209,1)  
      ,CHD_CYCLE_CODE_99        =   substring(importtext,210,2)  
      ,CHD_DATE_LAST_NONMON     =  CASE WHEN substring(importtext,214,6) = 000000 THEN NULL  
                                        ELSE substring(importtext,214,6)          END  
      ,CHD_PREVIOUS_CYCLE_CODE_99   =   substring(importtext,220,2)  
      ,CHD_DLVR_PONT_NR         =   substring(importtext,222,9)  
      ,CHD_DLVR_BAR1_NR         =   substring(importtext,231,19)  
      ,CHD_DLVR_BAR2_NR         =   substring(importtext,250,19)  
      ,CHD_CHARGE_DDA_CODE      =   substring(importtext,269,1)  
      ,CHD_TRANSIT_ROUTING_NO   =   substring(importtext,270,10)  
      ,CHD_CHECKING_ACCT_NO     =   CASE   
                                   WHEN substring(importtext,280,17) = ' '     THEN NULL  
                                      ELSE RTRIM(substring(importtext,280,17))    END  
  from stg   
  OPTION(maxdop 1)  
go  
set statistics time off  
go

outputs

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   
 Mar 18 2018 09:11:49   
 Copyright (c) Microsoft Corporation  
 Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19041: ) (Hypervisor)  
  
  
SQL Server parse and compile time:   
   CPU time = 0 ms, elapsed time = 2 ms.  
  
 SQL Server Execution Times:  
   CPU time = 5469 ms,  elapsed time = 5473 ms.  
SQL Server parse and compile time:   
   CPU time = 0 ms, elapsed time = 0 ms.

and

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)   
 Sep 23 2020 16:03:08   
 Copyright (C) 2019 Microsoft Corporation  
 Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19041: ) (Hypervisor)  
  
  
SQL Server parse and compile time:   
   CPU time = 0 ms, elapsed time = 2 ms.  
  
 SQL Server Execution Times:  
   CPU time = 5687 ms,  elapsed time = 5696 ms.  
SQL Server parse and compile time:   
   CPU time = 0 ms, elapsed time = 0 ms.

Terry Kaczmarski 1 Reputation point

2020-12-03T12:45:26.927+00:00

Thank you David.

Here are results 2019:

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)
Sep 23 2020 16:03:08
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

(1 row affected)

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 8 ms.

SQL Server Execution Times:
CPU time = 6218 ms, elapsed time = 6209 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

Completion time: 2020-12-03T07:23:23.8784230-05:00
Terry Kaczmarski 1 Reputation point

2020-12-03T12:46:14.027+00:00

And Results for 2016:

And

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 4 ms.

SQL Server Execution Times:
CPU time = 6032 ms, elapsed time = 6032 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-12-03T22:13:40.42+00:00
It appears that you got the same result as David. I guess then you to analyse what is different in your case. I note that David has OPTION (MAXDOP 1) in his script.

I tried his script on my machine, but with MAXDOP comment out. This iis just my desktop with lots of applications running and max server memory set to 4GB per instance. For SQL 2016 I got:

SQL Server Execution Times: CPU time = 11126 ms, elapsed time = 1907 ms.

On SQL 2019 I got:

SQL Server Execution Times: CPU time = 17358 ms, elapsed time = 2737 ms.

So SQL 2019 was slower in my case too, but not dramatically.

Share via

SQL2016 Upgrade to SQL2019 Long Running Query

7 answers

Your answer