Why would a scheduled backup run twice as fast as a non-scheduled backup on same Db?

VinceJ

I have a 600gb db that has a full backup done weekly and averages 67 minutes per run over 10 weeks. Using the RG backup interface on that same Db and writing to the same destination takes 130+ minutes. Why?

I'm manually running this at a similar time and the network is quiet as is the SQL instance.

petey2

If every other external factor is equal, one item that would affect backup speed from within SQL Backup is the compression level.  Are you using the same compression levels in the scheduled backup and the ad-hoc backup?  

VinceJ

There is no encryption or compression set for either run so in this case a nonfactor. I've used the same command with the only difference being the path in the job and in the adhoc sql and the adhoc takes twice the time.

petey2

Could you please post the SQL Backup logs of a scheduled backup and an ad-hoc backup here for us to take a detailed look?  Thanks.

VinceJ

here are the 2 log entries, the 1st is the fast run and I see there's 20 extra gb of data in the 2nd run but that shouldn't add 90 minutes.

SQL Backup log file 9.1.6.257
 
-SQL "BACKUP DATABASE [FranStore2004] TO DISK = '\\CG1SQLP02\Backup1$\CISBackup\<database>\<AUTO>.sqb' WITH CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 0, THREADCOUNT = 7, CHECK_PREFERRED_AG_REPLICA, SECONDARY_REPLICA_COPY_ONLY "
 
-----------------------  PROCESSES COMPLETED SUCCESSFULLY   --------------------
 
2020-02-20 3:09:00 PM: Backing up FranStore2004 (full database) on CIS2017P00 instance to:
2020-02-20 3:09:00 PM:   \\CG1SQLP02\Backup1$\CISBackup\FranStore2004\FULL_CIS2017P00_FranStore2004_20200220_150900.sqb
 
2020-02-20 3:09:00 PM: BACKUP DATABASE [FranStore2004]  TO VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC280', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC28001', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC
28002', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC28003', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC28004', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC28005', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7
-450E-8DB2-CFA141EFC28006' WITH BUFFERCOUNT = 28, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (FranStore2004), 2020-02-20 3:09:00 PM', DESCRIPTION = N'Backup on 2020-02-20 3:09:00 PM  Server: CG1SQLP02\CIS2017P00  Database:
 FranStore2004', FORMAT, CHECKSUM, COPY_ONLY
 
2020-02-20 3:58:31 PM: Backup data size    : 478.607 GB
 
2020-02-20 3:58:31 PM: Processed 53858632 pages for database 'FranStore2004', file 'Franstore2004_data' on file 1.
2020-02-20 3:58:31 PM: Processed 8865385 pages for database 'FranStore2004', file 'Franstore2004_log' on file 1.
2020-02-20 3:58:31 PM: BACKUP DATABASE successfully processed 62724017 pages in 2970.163 seconds (164.984 MB/sec).
2020-02-20 3:58:31 PM: SQL Backup process ended.

-----------------------------------------------------------------------------------------------------

 
SQL Backup log file 9.1.6.257
 
-SQL "BACKUP DATABASE [FranStore2004] TO DISK = '\\cg1redgtp01\testbackup\CIS\<database>\<AUTO>.sqb' WITH ERASEFILES_ATSTART = 1, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 0, THREADCOUNT = 7, CHECK_PREFERRED_AG_REPLICA,
 COPY_ONLY "
 
-----------------------  PROCESSES COMPLETED SUCCESSFULLY   --------------------
 
2020-02-20 4:05:07 PM: Backing up FranStore2004 (full database) on CIS2017P00 instance to:
2020-02-20 4:05:07 PM:   \\cg1redgtp01\testbackup\CIS\FranStore2004\FULL_CIS2017P00_FranStore2004_20200220_160507.sqb
 
2020-02-20 4:05:07 PM: BACKUP DATABASE [FranStore2004]  TO VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE691FD', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE691FD01', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE69
1FD02', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE691FD03', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE691FD04', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE691FD05', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4
-48AD-8AC6-70F5FCE691FD06' WITH BUFFERCOUNT = 28, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (FranStore2004), 2020-02-20 4:05:07 PM', DESCRIPTION = N'Backup on 2020-02-20 4:05:07 PM  Server: CG1SQLP02\CIS2017P00  Database:
 FranStore2004', FORMAT, CHECKSUM, COPY_ONLY
 
2020-02-20 6:39:18 PM: Backup data size    : 499.799 GB
 
2020-02-20 6:39:18 PM: Processed 53870048 pages for database 'FranStore2004', file 'Franstore2004_data' on file 1.
2020-02-20 6:39:18 PM: Processed 11631321 pages for database 'FranStore2004', file 'Franstore2004_log' on file 1.
2020-02-20 6:39:18 PM: BACKUP DATABASE successfully processed 65501369 pages in 9247.554 seconds (55.336 MB/sec).
2020-02-20 6:39:18 PM: SQL Backup process ended.

petey2

Thanks for posting the logs.

The backup to \\CG1SQLP02\Backup1$\CISBackup\FranStore2004\ was completed at the rate of 164.984 MB/sec, while the backup to \\cg1redgtp01\testbackup\CIS\FranStore2004\ was completed at 55.336 MB/sec. 

If you took a backup of a smaller database (5 GB or less), do you see the rate backup rates to the two network paths above?

VinceJ

I don't have a db that small on that box but I did run the test on the smallest I have and just like the above 1 runs at 176mb a second while the other is at 88mb a second. The 2 instances reside on the same host and the 2 drives are on the same shared storage array so there should be no difference between the backup speeds. If anything the 2nd cmd should be quicker as there's less activity on the 2nd instance. There's no humans on either instance and the 1st is the backside of an AAG mirror so is receiving prod updates and does backups. The other, slower backup is a snapshot of the same instance config with a different name so in my mind they should be equal or close to it.

petey2

So the difference in backup rates isn't because one is ran as a scheduled backup, and the other as an ad-hoc backup, but because the backup files are being stored to different network paths?

How long does it take to copy a 1 - 2 GB file from the SQL Server instance box to \\CG1SQLP02\Backup1$\CISBackup\FranStore2004\ and \\cg1redgtp01\testbackup\CIS\FranStore2004\ respectively, using Windows Explorer (if you can get a constant speed)?

VinceJ

That's what i thought so had the network guy do that test and he's reporting 240+mb per second on either.

petey2

Could you please try running the backup of the smaller database to the 'slower' network path using FILEOPTIONS of 8 and 16 and see if there is any difference  in the backup rate? E.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE ... WITH FILEOPTIONS = 8"'
EXEC master..sqlbackup '-sql "BACKUP DATABASE ... WITH FILEOPTIONS = 16"'

If there is no improvement, there is a setting named SetEOF that changes the way files are allocated, but the setting needs to be made in the registry (also, I'm not sure if this setting exists in SQL Backup 9.1).  You would create a DWORD-type setting in SQL Backup's registry node (HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>), and set its value to 1.  Then try running a test backup with FILEOPTIONS values of 8 and 16 as per above.

VinceJ

Run 1 as the standard command I've been using returns:
 BACKUP DATABASE successfully processed 4008260 pages in 377.103 seconds (83.039 MB/sec).
Run 2 with fileoptions = 16 returns:
BACKUP DATABASE successfully processed 4008260 pages in 374.802 seconds (83.549 MB/sec).
Run 3 with fileoptions = 8 is actually worse with:
BACKUP DATABASE successfully processed 4008260 pages in 425.041 seconds (73.674 MB/sec)

petey2

As the SetEOF option doesn't seem to be in version 9.1, could you please download the SQL Backup Test Utility (https://download.red-gate.com/support/sql_backup/Testing_Utilities/SBaTU.zip) to run the disk writes test?

Unzip the contents to a folder on the instance.
Start the test application, select the 'Tools > Run extended disk writes test' item.
Select the network path as the backup folder, set a file size of 512 MB, a 'medium' block size.  Don't select the 'Run once only' option.

Start the tests and send me the results (peter.yeoh@red-gate.com).  Thanks.

VinceJ

results sent.