Enhancing SQL Backup Performance

Performance tuning parameters can increase efficiency of backup operation by avoiding throughput bottlenecks. If you upgraded your client from a previous SnapProtect release, you can optimize the SQL backup performance by configuring the following performance tuning parameters and the number of streams used for backups.

  • Block Size

    MediaAgents can write to media using different block sizes if the operating system that is associated with the MediaAgent on which the library is configured supports a higher block size. Increasing the block size increases the speed of write operations to tape media. The default block size for write operation is 65,536 bytes (or 64 KB). To increase the block size, enter any value from 512 bytes to 65,536 bytes.

  • Buffer Count

    Buffer count is the number of data buffers used to reserve bandwidth during a backup operation. You can increase the data transfer throughput during backup by increasing the buffer count value.
    When you increase the buffer count, more bandwidth is consumed during backup. This may degrade the overall backup performance. So, before increasing the buffer count, verify that adequate bandwidth is available. The default value of buffer count is 20. To increase the buffer count, enter any value from 1 to INT_MAX, where INT_MAX is the maximum value of an INT on the platform being used.

    Note: Between the number of streams and the buffer count, the parameter with a greater value is considered during a backup operation.

  • Maximum Transfer Size

    Maximum transfer size is the maximum amount of data transferred at a time during a backup operation. The default value is 2,097,152 bytes (or 2048 KB). You can enter any value from 65,536 bytes (or 64 KB) to 4,194,304 bytes (or 4 MB). All data is transferred in multiples of 64 KB.

    Note: The value of Application Read Size should be equal or greater than the value of Maximum Transfer Size. Otherwise, the backups might fail.

Following is the tabular presentation of performance tuning parameters for enhancing SQL backup performance:

Parameter Default Value Supported Value
Maximum size of data blocks used during backups 65536 bytes 512 bytes to 65536 bytes
Number of buffers used to reserve bandwidth for data transfer 20 1 to INT_MAX
Maximum amount of data to be transferred at a time during backups 2097152 bytes 65536 bytes to 4194304 bytes