Backup and restore processes using high percentage of CPU resources

Category: Troubleshooting & error messages
Date: 25 Feb 2010
Product: SQL Backup
When using compression level 4, SQBCoreService.exe can use a large percentage of the processor during a backup.

With compression 1-3, typically the hard disk is the limiting factor and so the CPU will not be unnecessarily burdened. With "Level 4" compression, however, the CPU is usually the limiting factor - and it will utilise as much as possible. If you desire very high compression AND non-exclusive usage of the CPU you can either:

  · Use the THREADPRIORITY parameter to adjust SQB's importance. Lower values will provide smaller slices of CPU to the process:
      · THREADPRIORITY=0 (Idle--not recommended)
      · THREADPRIORITY=1 (Very low)
      · THREADPRIORITY=2 (Low)
      · THREADPRIORITY=3 (Normal)
      · THREADPRIORITY=4 (High)
      · THREADPRIORITY=5 (Very High)
      · THREADPRIORITY=6 (Time Critical -- not recommended)

  · Set the backup process to use an optimum number of threads - on a dual core machine this hard limits SQB to 50% of the CPU (25% on a quad core, etc). For computers with multiple processors, the optimum number of threads is (number of CPUs and cores)-1, leaving one processor for SQL Server.
      · THREADCOUNT=x
      · Backup to multiple files, for instance "BACKUP DATABASE [x] TO DISK=[...], DISK=[...]..."