SQL2016 Upgrade to SQL2019 Long Running Query

Question

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?

Answer 1

Sorry, I did not mean "converting to SSMS", I meant "converting to SSIS". [Too many S's, they all look the same :) thanks for pointing that out]

My SQL2019 instance is at CU8. After the initial poor performance tests, I rebuilt all the databases from scratch as opposed to restoring them.

As far as compatibility level, on SQL2019 it was at 150. When I change the DB to 130, it still runs for 10 minutes to load 1 million rows.

Answer 2

Hi @Terry Kaczmarski ,

Your thoughts?

1. After your upgrade, you need do as next,please verify you have done:

• Confirm that the compatibility level of the database has been upgraded to the latest database version

• Rebuild the index on the database

• Re-update the statistics on the database

2.Please change the MAXDOP to 1 and CTFP to 5 to have a test:

USE dbname ;    
GO     

EXEC sp_configure 'show advanced options', 1;    
GO    

RECONFIGURE WITH OVERRIDE;    
GO    

EXEC sp_configure 'max degree of parallelism', 1;    
GO    

RECONFIGURE WITH OVERRIDE;    
GO    


USE dbname;    
GO    

EXEC sp_configure 'show advanced options', 1 ;    
GO   

RECONFIGURE    
GO    

EXEC sp_configure 'cost threshold for parallelism', 5 ;    
GO    

RECONFIGURE    
GO    

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

The recommended upgrade procedure is to not change the database compatibility level after an upgrade until you have a performance baseline with Query Store enabled.

https://learn.microsoft.com/en-us/sql/database-engine/install-windows/change-the-database-compatibility-mode-and-use-the-query-store?view=sql-server-ver15

Answer 4

Hi @Terry Kaczmarski ,

SQL2019 Execution wait stats:

wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
PAGEIOLATCH_SH 4 2 1 0
MEMORY_ALLOCATION_EXT 111 0 0 0

SQL2016 Execution wait stats on same machine:
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
SOS_SCHEDULER_YIELD 2451 4 0 3
MEMORY_ALLOCATION_EXT 81 0 0 0

1.Please check whether your memory is enough, set max server memory for SQL Server is 0.7 * Server Memory; And if it is possible, allocate one server is special for SQL Server,if not possible, make sure other applications in your server have enough memory and they won't preempt Sql Server's memory when they are running.

It should be emphasized that the two most common types of PAGEIOLATCH_x waits are PAGEIOLATCH_SH and PAGEIOLATCH_EX. PAGEIOLATCH_SH often happens when the user is about to access a data page, while SQL Server wants to read this page from disk to memory. If this page is frequently accessed by users, then in the final analysis, the problem is that the memory is not large enough to always cache the data page in the memory. Therefore, it is often the memory pressure that triggers SQL Server to do a lot of page reading work, which causes the disk read bottleneck. The disk bottleneck here is often a by-product of the memory bottleneck.

These tables do not have indexes. 100% load into a flat table [varchar(6000)], then 100% load of the data into a table with 950+ data columns.

Please create a clustered index for your table,this will improve the performance.

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

Thank You Mia and Erland

Server specifics:

• This is a dedicated server to SQLSERVER 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

The SQL2019 install is configured with max memory of 1331200 MB (1300 GB 85% of total)
name minimum maximum config_value run_value
max server memory (MB) 128 2147483647 1331200 1331200

The SQL2016 install is configured with default memory value since this install will be dropped.

Source Table: (1 Million rows all with ImportRecordType = '01BASE')
ImportRecID int IDENTITY(1,1) NOT NULL, (Clustered IX on this)
ImportText varchar(8000) NULL,
ImportRecordType char(7) NULL,
ImportAccount char(28) NULL,
ImportKey char(33) NULL
PK added to table and tests rerun in both 2019/2016. No impact to results.43827-sql2019-executeplan-xml.txt43852-sql2016-executeplan-xml.txt

T-SQL command:
Insert into Target SELECT ... from source where ImportRecordType = '01BASE'

XML formatted execution plan is attached.