Knowledge Base

Improving performance by leveraging multi-processor hardware

Category: How do I?
Date: 13 Jul 2009
Product: SQL Backup
Versions: 4,5,6

SQL Backup can take advantage of multiple cores and processors.

In SQL Backup version 4.x you would be required to perform split-file backups in order to use multiple threads -- each thread would have affinity towards a different processor or core than the previous thread, so backups that run on multiple threads would spread the workload across multiple processors. This will improve performance and in some case a lot, but you won't have a single backup file as a result. You can do this via the Backup wizard GUI, or in script e.g.

TO DISK = ''C:/BackupFiles/<AUTO>_01.sqb'', DISK = ''C:/ BackupFiles/<AUTO>_02.sqb''
(the number of files corresponds to the number of threads).

Alternatively you may use the THREADS option, which creates multiple automatically-named file names.

The THREADS option in SQL Backup version 5 will use multiple threads, utilizing multiple cores and processors, but it will write to a single file. This may incur a noticeable performance overhead, so specifying multiple files will still deliver the maximum performance.

The recommended number of threads to use on multi-processor or multiple-core systems is (number of processors/cores)-1, leaving one processor to SQL Server.