SQL Server Database Backup Performance Test

This is not a very comprehensive test per se,  only comparing backup duration among uncompressed backup to a single file, compressed backup to a single file and a compressed backup to multiple files. 

As we know data compression is a CPU intensive operation so I am also comparing the impact of each on the overall CPU utilization.

Test Environment:

SQL Server Version: 2016 (SP3) Enterprise Edition
OS Platform: VMWare VM
OS Version: Windows 2016 Standard
CPUs: Intel Xeon CPU E5-2699 v4 @ 2.2GHz, 2195 Mhz
Total Server Memory: 16GB
SQL Server Max Memory: 12.5GB
Database Size: 150GB

Disk I/O subsystem: SQL Data and Log files are on separate RDMs. At the moment, I have not measured their performance limits. 

Backup Commands:

Note that I have added two non-default backup parameters to each backup command 1) MAXTRANSFERSIZE 2) BUFFERCOUNT

/*
Backup file size uncompressed: 152
Backup file size Compressed: 35GB
*/

SET NOCOUNT ON

SELECT GETDATE() [Batch Start Time]
GO
DECLARE @start_time datetime = getdate()
PRINT 'Starting database backup to uncompressed file destination....'
BACKUP DATABASE [DW360DB] TO  
DISK = N'O:\SQL2016AG01\BACKUP\DW360DB_20230714_uncompressed.bak'
WITH  COPY_ONLY, NOFORMAT, INIT, NO_COMPRESSION, NAME = N'Uncompressed Full DB Backup', 
MAXTRANSFERSIZE = 4194304, -- 4194304  = 4MB
BUFFERCOUNT = 50,
SKIP, NOREWIND, NOUNLOAD, STATS = 5
select DATEDIFF(SECOND, @start_time, getdate()) [UnCompressed Backup Duration Seconds]

GO
WAITFOR DELAY '00:00:10'

PRINT 'Starting database backup to compressed file destination....'
DECLARE @start_time datetime = getdate()
BACKUP DATABASE [DW360DB] TO  
DISK = N'O:\SQL2016AG01\BACKUP\DW360DB_20230714_compressed.bak'
WITH  COPY_ONLY, NOFORMAT, INIT, COMPRESSION, NAME = N'Compressed Full DB Backup', 
MAXTRANSFERSIZE = 4194304, -- 4194304  = 4MB
BUFFERCOUNT = 50,
SKIP, NOREWIND, NOUNLOAD, STATS = 5
select DATEDIFF(SECOND, @start_time, getdate()) [Compressed Backup Duration Seconds]

GO
WAITFOR DELAY '00:00:10'

PRINT 'Starting database backup to compressed, multiple files destination....'
DECLARE @start_time datetime = getdate()
BACKUP DATABASE [DW360DB] TO  
DISK = N'O:\SQL2016AG01\BACKUP\DW360DB_20230714_compressed_f1.bak',
DISK = N'O:\SQL2016AG01\BACKUP\DW360DB_20230714_compressed_f2.bak' ,
DISK = N'O:\SQL2016AG01\BACKUP\DW360DB_20230714_compressed_f3.bak',
DISK = N'O:\SQL2016AG01\BACKUP\DW360DB_20230714_compressed_f4.bak' 
WITH  COPY_ONLY, NOFORMAT, INIT, COMPRESSION, NAME = N'Compressed FUll DB Backup to Multiple Files', 
MAXTRANSFERSIZE = 4194304, -- 4194304  = 4MB
BUFFERCOUNT = 50,
SKIP, NOREWIND, NOUNLOAD, STATS = 5
select DATEDIFF(SECOND, @start_time, getdate()) [Compressed Backup To Multiple Files]
GO
SELECT GETDATE() [Batch End Time]

Backup Duration:

Uncompressed Backup to a Single Backup File: 21 Minutes and 17 Seconds

Compressed Backup to a Single Backup File: 06 Minutes and 41 Seconds

Compressed Backup to Multiple Backup Files: 04 Minutes and 04 Seconds

Impact on CPU Utilization:

Conclusion: 

Compared to uncompressed backup, using the compressed backup to multiple backup files, I was able to reduce the overall backup time from 21 minutes down to only 4 minutes, a massive 80% reduction in backup duration, provided you can live with the impact on CPU and thereby on database performance. 

This is only a single test so I am not just yet ready to call this a typical rate of performance improvement to expect.