Reducing load database Time

Use the optimize dump for faster load configuration parameter to optimize dump database for a faster load database time.

The time it takes to perform a load database (either full or cumulative), is dependent on two primary factors:

The time it takes for Backup Server to physically copy the pages from the dump archive into the database.
The time it takes for load database to recover the loaded database pages. Recovery time is largely dictated by the amount of work that needs to be redone.

By reducing the amount of work that load database recovery has to redo, the time it takes to perform a load database decreases. The amount of recovery work that needs to be redone is related to the volume of transactional work that is on-going while dump database (full or cumulative) copies database pages using Backup Server, from the database into the dump archive. In versions earlier than 15.7 SP121, a single iteration in which all the pages that must be copied by Backup Server to the archive is done by dump database.

SAP ASE supports multiple iterations. That is, at the end of the first iteration, changes that have been made to the database while the first iteration was active, are copied by Backup Server to the archive in a second iteration. The set of changes in each iteration is less than those in the previous iteration, giving load database less changes to recover. A maximum of four iterations can be performed.

The optimize dump for faster load configuration parameter is used to configure this behavior. The syntax is:

sp_configure “optimize dump for faster load”, <percentage>

When <percentage> is set to 0, a single iteration is done. This is the same behavior as in versions of SAP ASE earlier than 15.7 SP121.

When <percentage> is set to a non-zero value, it represents the minimum percentage of data pages (not log pages) in the database that have changed relative to the total number of in-use data pages in the database since the start of the current iteration in order for the next iteration to be performed (up to a maximum of 4 iterations).

For example, when <percentage> is set to a value of 2, and if the percentage of changed data pages in the database since the start of the first iteration is greater than 2% of the total in-use data pages in the database, then at the end of the first iteration of copying pages, another iteration of copying pages is performed. In this case, the second iteration of copying pages starts, and a similar test as to whether to start a third iteration is performed at the end of the second iteration.

When optimizing a dump for faster load, it will take longer to perform a dump database.