What are the challenges you face when working across database platforms? Take the survey

Question on number of threads option for Compression

nycgagsnycgags Posts: 2
edited April 23, 2015 9:20PM in SQL Backup Previous Versions
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."


  • Options
    peteypetey Posts: 2,358 New member
    The more threads you use, the faster your backups will complete (up to a certain point), at the cost of increased memory and CPU requirements. If you're concerned about CPU cycles, here are some things you may want to consider.

    - 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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.