SQL Server DB Backup - Backup Compression and Timing

Question

Hi All,
We are using PeopleSoft Application (HRMS and FSCM) and it's running on SQL Server 2014 Enterprise Edition.
Here the FSCM DB Size is 440GB and HRMS DB Size is 395 GB.
We are taking daily DB Backup's for both HRMS and FSCM Database and using the "Compress Backup" under the Backup Settings.

Here is my question..
When i compare both the DB's FSCM Database is much larger when compared with HRMS Database (i.e around 45 GB additional on FSCM Db).

1. When taking backup, FSCM Backup job gets completed around 40 minutes and HRMS Backup Jobs takes nearly 1 hour and 45 Minutes to complete.
   Please note that during the Backup Job timings, we dont have any workload on the Database (i.e.. all the PeopleSoft Job's and DB's Jobs are disabled during this backup timing). My question is why the HRMS db backup job is taking much longer time when compared with the FSCM backup job (i.e triple the time it takes). Also if you could note the HRMS Database is smaller when compared to FSCM database and HRMS DB Backup job is taking triple the time when compared to FSCM Backup Job timing.
   Please suggest.
2. If i refer the Backup file, FSCM Db backup file size is 138 GB and the HRMS DB backup file size is 254 GB. Here we have set to COMPRESS BACKUP for both the Database, but not sure why the HRMS DB backup file is larger when compared to FSCM db backup file size.
   If you could notice the Physical db size, HRMS Physical DB is smaller when compared to FSCM Physical db but the backup file is larger here..
   Please suggest.

regards
Dhana

Answer 1

My question is why the HRMS db backup job is taking much longer time when compared with the FSCM backup job (i.e triple the time it takes)

Without any concrete performance data it's hard to say but I'll make an educated guess that it's due to the size of the final backup (in this case 254GB which is almost twice the backup size of the other database at 138 GB), so writing double the data to the disk is most likely taking up a good portion of the extra time... but again no performance data from the disks, cpu, memory to show this.

Here we have set to COMPRESS BACKUP for both the Database, but not sure why the HRMS DB backup file is larger when compared to FSCM db backup file size.

Compression results vary based on the type of data and the amount of entropy in the data, among other things. This is just telling you that the data in one database is apparently better for compression than another, so it has a better compression ratio. A quick example is that certain types of text usually compresses very well, but numeric digits may not as there may not be much uniformity to their layout. It also depends on the algorithm type, dictionary size, etc., which in this case you can't control.

There isn't really a whole lot you can do except figure out which item is the bottleneck in the setup (disk/memory/cpu/etc) and tune/get faster/better/etc. as 138GB in 40 mins = 58MB/sec and 254GB in 105 mins = 41 MB/sec which is very slow by most modern standards.

Answer 2

Hi @Dhanasekaran ,

> but not sure why the HRMS DB backup file is larger when compared to FSCM db backup file size.

For compressed backups, the size of the final backup file depends on how compressible the data is. A variety of factors can impact the compression ratio obtained. Major factors include:

• The type of data. Character data compresses more than other types of data.
• The consistency of the data among rows on a page.
• Whether the data is encrypted
• Whether the database is compressed.

Please refer to Backup Compression (SQL Server) to get more information.

> My question is why the HRMS db backup job is taking much longer time when compared with the FSCM backup job (i.e triple the time it takes)

The HRMS DB backup file size (254 GB) is almost twice the FSCM Db backup file size(138 GB), it will spend more time to write data to disk. And other factors will impact the time for writing data to disk, such as the number of threads for writing data ect.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 3

To answer for the above questions..

1. We are not encrypting the database and we don't have any encryptions for the backup.

Below is the log for the finance db from the DEV Database
Processed 51435488 pages for database 'FIN92DEV', file 'FIN91PRD_data' on file 1.
Processed 7 pages for database 'FIN92DEV', file 'FIN91PRD_log' on file 1.
BACKUP DATABASE successfully processed 51435495 pages in 1167.316 seconds

Below is the log for the HRMS db from the DEV Database
Processed 49261408 pages for database 'HCM92DEV', file 'HCM91PRD_data' on file 1.
Processed 1 pages for database 'HCM92DEV', file 'HCM91PRD_log' on file 1.
BACKUP DATABASE successfully processed 49261409 pages in 1682.348 second

If you compare both the Processed pages, Finance db (51435488) is larger than HRMS Db (49261408).
Here HRMS has consumed much time when compared with Finance even though the Processed Pages is less compared with Finance db..

Any thoughts. Cathyji??

Answer 4

To answer for the above questions..

1. We are not encrypting the database and we don't have any encryptions for the backup.

Below is the log for the finance db from the DEV Database
Processed 51435488 pages for database 'FIN92DEV', file 'FIN91PRD_data' on file 1.
Processed 7 pages for database 'FIN92DEV', file 'FIN91PRD_log' on file 1.
BACKUP DATABASE successfully processed 51435495 pages in 1167.316 seconds

Below is the log for the HRMS db from the DEV Database
Processed 49261408 pages for database 'HCM92DEV', file 'HCM91PRD_data' on file 1.
Processed 1 pages for database 'HCM92DEV', file 'HCM91PRD_log' on file 1.
BACKUP DATABASE successfully processed 49261409 pages in 1682.348 second

If you compare both the Processed pages, Finance db (51435488) is larger than HRMS Db (49261408).
Here HRMS has consumed much time when compared with Finance even though the Processed Pages is less compared with Finance db..

Any thoughts. Cathyji??