Options
Question on number of threads option for Compression
nycgags
Posts: 2
The documentation states to use 1 less thread than the number of processors in the system, so if the system has 4 processor to use 3 threads.
Should cores be considered the same thing as processors, i.e. on a system with a Xeon E5-2680 as described below:
http://ark.intel.com/products/64583/Int ... -Intel-QPI
[*] 2 processors
[*] 8 cores
[*] 16 threads
How many threads should we use?
On a related note my client is seeing his server's cpu usage spike during backups (which are scheduled after hours), cpu usage is peaking > 90% which is causing alerts to go off. Using the configuration above they are using 3 compression setting (out of 4) and have threads set at 6. I recommended turning off compression for a night to see what effect this has on CPU. Any advice would be appreciated, even if it is "It is normal for CPU usage to peak during backups using compression."
Should cores be considered the same thing as processors, i.e. on a system with a Xeon E5-2680 as described below:
http://ark.intel.com/products/64583/Int ... -Intel-QPI
[*] 2 processors
[*] 8 cores
[*] 16 threads
How many threads should we use?
On a related note my client is seeing his server's cpu usage spike during backups (which are scheduled after hours), cpu usage is peaking > 90% which is causing alerts to go off. Using the configuration above they are using 3 compression setting (out of 4) and have threads set at 6. I recommended turning off compression for a night to see what effect this has on CPU. Any advice would be appreciated, even if it is "It is normal for CPU usage to peak during backups using compression."
Comments
- compression level
Compression levels 3 and 4 are fairly CPU intensive algorithms. Consider if levels 1 and 2 are acceptable trade-offs, given their larger backup size.
- number of threads
Backup speed is also determined by how fast data can be read from and written to your disks. You may already be hitting the physical limits of your disks with less threads, so increasing the number of threads will not yield any speed improvements. You might consider reducing the number of backup threads to determine is this is the case.
- database data
Data that's not compressible e.g. jpeg images, compressed media files, will take up more CPU cycles to process. What's the current compression ratio as reported by SQL Backup?
See if this troubleshooting document helps.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8