2

The setup:

Server 1 - SQL Server 2016 Enterprise - 13.0.6430.49 on Windows Server 2016 Datacenter

Server 2 - SQL Server 2019 Enterprise - 15.0.4280.7 on Windows Server 2019 Datacenter

Both servers are in Azure Both backup directly to storage account - URL

Backup is done with 64 stripes with these options:

WITH INIT 
,FORMAT 
,COMPRESSION 
,NOUNLOAD 
,SKIP 
,STATS = 10 
,MAXTRANSFERSIZE = 4194304
,BLOCKSIZE = 65536

I am in the process of migrating a 19TB database from Server 1 to Server 2.

Server 2 was built identically to Server 1, same SQL configurations etc.

The 19TB DB in question compressed backup size is 6TB.

On Server 1 (SQL 2016), the backup takes 5 hours and averages 700MB/sec

On Server 2 (SQL 2019), the backup takes 12 hours and averages 300MB/sec

I am using the exact same script to do the backup and it goes to the same storage account.

My observation: On Server 2, the backup runs at 700MB/sec for the first 2 hours and then at exactly 2 hours, it starts slowing down. The wait type recorded is ASYNC_IO_COMPLETION

Besides the version differences, the only other difference is that Server 2 is part of an AG so it's clustered.

Here is a snippet of what happens - Data Disk Read Bytes/Sec enter image description here

Are there any known issues with backup performance in SQL 2019?

Any ideas

Thanks

9
  • What tier in Azure is the old server vs the new one?
    – J.D.
    Jun 11, 2023 at 16:51
  • They're both Standard E64-32s v3 Jun 11, 2023 at 19:39
  • Old server has 12 x P40 disks pooled to make a 24TB disk for data files. New server has 15 x P40 disks pooled to make a 30TB disk for data files. Both configured with 3 x column width Jun 11, 2023 at 19:41
  • 2
    Have you excluded subsystem issues by writing a 6TB file to both systems and comparing timings using IOMeter or CrystalDiskMark or similar? Jun 12, 2023 at 0:04
  • ^Seconded using CrystalDiskMark. It'll help rule out if the issue is internal to your SQL Server instance or not, pretty easily.
    – J.D.
    Jun 12, 2023 at 1:27

0

Your Answer

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

Browse other questions tagged or ask your own question.