sp_dumpoptimize

Description

Specifies the amount of data dumped by Backup Server during the dump database operation.

Syntax

sp_dumpoptimize [ ’archive_space = {maximum | minimum | default }’ ]

sp_dumpoptimize [ ’reserved_threshold = {nnn | default }’ ]

sp_dumpoptimize [ ’allocation_threshold = {nnn | default }’ ]

Parameters

Examples

Example 1

This causes the whole database to be dumped:

sp_dumpoptimize 'archive_space=maximum'

Backup Server: 4.172.1.1: The value of 'reserved pages threshold' has been set to 0%.
Backup Server: 4.172.1.2: The value of 'allocated pages threshold' has been set to 0%.

Example 2

This causes only the allocated pages to be dumped, thereby resulting in the smallest archive image:

sp_dumpoptimize 'archive_space=minimum'

Backup Server: 4.172.1.1: The value of 'reserved pages threshold' has been
set to 100%.
Backup Server: 4.172.1.2: The value of 'allocated pages threshold' has been
set to 100%.

Example 3

This causes the reserved threshold to be set to 85 percent and the allocation threshold to be set to 40 percent:

sp_dumpoptimize 'archive_space=default'

Backup Server: 4.172.1.1: The value of 'reserved pages threshold' has been
set to 85%.
Backup Server: 4.172.1.2: The value of 'allocated pages threshold' has been
set to 40%.

Example 4

Those disks in the database with a percentage of reserved pages that is greater than or equal to 60 percent are dumped without reading allocation pages on this disk. For the remaining disks, the allocation pages are read, and the last set value for the allocation_threshold is used. If the allocation_threshold was not set after Backup Server was started, default allocation_threshold of 40 percent is used:

sp_dumpoptimize 'reserved_threshold=60'

Backup Server: 4.172.1.3: The value of 'reserved pages threshold' has been
set to 60%.

Example 5

Causes the reserved threshold to be set to 85 percent. It does not affect the allocation page threshold:

sp_dumpoptimize 'reserved_threshold=default'

Backup Server: 4.172.1.3: The value of 'reserved pages threshold' has been
set to 85%.

Example 6

Allocation pages are read for those disks whose reserved page percentage is less than the last set value for the reserved_threshold and if an allocation unit has 80 percent or more pages allocated, then the whole allocation unit is dumped:

sp_dumpoptimize 'allocation_threshold=80'

Backup Server: 4.172.1.4: The value of 'allocated pages threshold' has been
set to 80%.

Example 7

Causes the allocation page threshold to be set to the default of 40 percent. It does not affect the reserved pages threshold:

sp_dumpoptimize 'allocation_threshold=default'

Backup Server: 4.172.1.4: The value of 'allocated pages threshold' has been
set to 40%.

Example 8

Those disks in the database whose percentage of reserved pages is greater than or equal to 60 percent are dumped without reading allocation pages on this disk. For the remaining disks, the allocation pages are read and if an allocation unit has 30 percent or more pages allocated, then the whole allocation unit is dumped:

sp_dumpoptimize 'reserved_threshold=60', 'allocation_threshold=30'

Backup Server: 4.172.1.3: The value of 'reserved pages threshold' has been
set to 60%.
Backup Server: 4.172.1.4: The value of 'allocated pages threshold' has been
set to 30%.

Example 9

This displays the current value of the thresholds:

sp_dumpoptimize

Backup Server: 4.171.1.1: The current value of 'reserved pages threshold'
is 60%
Backup Server: 4.171.1.2: The current value of 'allocated pages threshold'
is 30%.

Usage

• When you set a threshold using sp_dumpoptimize, this threshold acts on each individual device that the database resides on.

• When you set values with sp_dumpoptimize, those values are immediately in affect without the need to restart Backup Server. However, the changes are effective only until the Backup Server is restarted. When Backup Server is restarted, the default values are used.

• If you issue sp_dumpoptimize multiple times, the thresholds specified by the last instance are used by later dumps. For example, if you first set the reserved_threshold value, and later issue archive_space=maximum, then that value overwrites the previous value you set for reserved_threshold.

• Dumps of different databases can use different thresholds by changing the sp_dumpoptimize values before each database dump.

• The optimal threshold values can vary from one database to another. Therefore, the performance of a dump depends on both the I/O configuration and the amount of used space in the database. The DBA can determine the appropriate configuration for a database by experimenting with dumps using different values and choosing the one that results in the shortest dump time.

• You can use sp_dumpoptimize for both local and remote dumps.

• sp_dumpoptimize has no effect on the performance of a transaction log dump or a load. Therefore, it need not be issued before dump transaction, load database or load transaction operations.

• If sp_dumpoptimize is issued without any parameters, the current value of the thresholds is displayed on the client.

• On configurations in which the archive device throughput is equal to or higher than the cumulative throughput of all the database disks, using archive_space=maximum may result in a faster dump. However, on configurations in which the archive device throughput is less than the cumulative throughput of all the database disks, using this option may result in a slower dump.

• The option names and the values for this procedure can be abbreviated to the unique substring that identifies them. For example, ar = ma is sufficient to uniquely identify the option archive_space=maximum.

• There can be zero or more blank space characters around the equal sign (=) in the option string.

• The option names and their values are case insensitive.

The default values for the thresholds are:

• Reserved pages: 85%

• Allocation pages: 40%

If the device fragment of the database has a reserved pages percentage that is:

• Greater than or equal to the reserved threshold – then all the blocks on this device that pertain to this database are dumped.

• Less than the reserved threshold – then Backup Server starts checking each allocation unit on this device for the allocation percentage. If the cumulative allocation percentage is:

  • Less than the allocation threshold – then it would only dump those pages with data written on it

  • Greater than the allocation threshold – then whole allocation unit would be dumped.

Permissions

Only the System Administrator, the Database Owner, or users with the Operator role can execute sp_dumpoptimize.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

See also