BLOCK FACTOR specifies the number of blocks to write to the archive device at one time. This parameter also controls the amount of memory used for buffers during the backup, and has a direct impact on backup performance. The effects of the block factor are a function of disk subsystem speed, tape speed, and processor speed.
| Platform | |
|---|---|
|
UNIX-like operating systems |
Set BLOCK FACTOR to at least 25 (default). With this setting, BACKUP can buffer data for most tape drives, with enough data in memory that drives are kept busy constantly throughout the backup. On AIX, use the System Management Interface Tool (SMIT) to display and change the current block mode of any tape device that you plan to use for backups. |
|
Windows |
On Windows, the default BLOCK FACTOR is computed based on the database block size. The default usually achieves maximum throughput on Windows. Because of the way Windows handles tape devices, increasing the BLOCK FACTOR may not lead to faster backups. |
See your platform operating system documentation for information about your platform’s optimal I/O size and block factor.
During database verification, sp_iqcheckdb reads every database page in use, which consumes most of the database server's time. I/O remains as efficient as possible, but other concurrent activities may run more slowly than usual.
To limit DBCC CPU use, set the resources resource-percent parameter, which controls the number of threads with respect to the number of CPUs. If resource-percent = 100 (the default value), there is one thread per CPU. If resource-percent > 100, then there are more threads than CPUs, which might increase performance for some machine configurations.
Setting the CRC parameter to OFF deactivates 32-bit cyclical redundancy checking on a per-block basis, which improves the speed of backup and restore operations.
With CRC set to ON (default), the checksums computed on backup are verified during any subsequent RESTORE operation, affecting the performance of both commands. If you turn off this checking, remember that you sacrifice a greater assurance of accurate data in exchange for faster performance.
The WITH COMMENT parameter lets you specifies a string as long as 32KB as part of the header information for the backup archive. If you omit this option, BACKUP enters a NULL. You can view the comment string by executing a RESTORE DATABASE... FROM... CATALOG ONLY or by displaying the backup log, backup.syb.
BACKUP makes a full backup of the catalog store at the start of every backup, both full and incremental. Ordinarily, the catalog store is quite small, containing only the system tables, metadata, and other information that SAP Sybase IQ needs to manage your database. However, you can create non-IQ tables in the catalog store.
To improve performance keep any non-SAP Sybase IQ data in a separate SAP Sybase SQL Anywhere®-only database, rather than in the catalog store. BACKUP copies only the latest committed version of the database. Other version pages used by open transactions are not backed up.
You may find that it is faster and more efficient to create backups on disk, and then spool them onto tape for archival storage. If you choose this approach, unspool the data onto disk before restoring it.