7.1 Multiple Threads question

PDinCA · June 28, 2012 1:44PM

Having run 7.1 for the first time and seen the elapsed time reduced for a Full backup of a 300GB database using compression level 4, 7 threads and Verify, I noticed a Processor Utilization Alert from SQL Monitor at the same time as this backup.

The Reg Date SQL Backup Core Service (SQBCoreService) ate 86.8% of the CPU resources, maxing the CPU to 100% utilization. It finally gave up hogging the CPU after 5 separate Alerts were emitted and 42 minutes of the total run-time of 50 had elapsed.

Firstly, it's a good job there were no users on the system at midnight EDT!

Secondly, this isn't a good thing

Would you please recommend how one can reduce the CPU percentage hogged by the new and obviously improved SQBCoreService?

Some factors you might be interested in:

1. We have ample CPU to spare "normally". Approx 5% to 20% max usage daylong is our norm. 2. We have an Indian office that will use CPU during our U.S. nighttime, and their calculations are cpu hogs, too, so we need to give them some headroom. 3. We are utterly disk-space-challenged, hence the use of compression level 4. Thanks for your help,

Stephen

petey · June 29, 2012 6:28AM

There isn't a way to directly throttle CPU utilization in SQL Backup. Indirectly, you could do it by using a number of backup threads lower than the number of CPU cores on your server. This frees up the other cores to service other tasks.

It's my understanding that on a system where most tasks are running at normal thread priority, SQL Backup will not hog all the CPU cycles as Windows will allocate CPU cycles (more or less equally) to all tasks running at the same thread priority. So if you have other CPU intensive tasks running simultaneously as SQL Backup, you should see total CPU utilitization hit 100%, but not all the 100% will be used by SQL Backup.

PDinCA · June 29, 2012 12:28PM

Hi Peter,

We have 16 logical CPUs on the server and only 7 allocated to SQL Backup in our attempt to throttle it back somewhat as we had 100% cpu issues from Dec 2010 for about 7 months at a time when we had replication and other stuff running, which have now been removed.

We haven't seen the Processor Utilization Alert, other than once a month for the Key Performance Indicator sweep at month end, so the sudden increase in utilization on an unchanged instance, apart from installing the 7.1 server components, was quite a surprise as you might imagine.

I'll keep an eye on it.

I read the Help section on the Compression Analyzer and the hoops one must jump through to get to "the optimum throughput". Any chance RG could do some further automation of this as it's rather a suck-it-and-see methodology at present...

I cancelled the Compression Analyzer after about 5 minutes as I could see that there is was a significant justification for using level-4. However, this raised a "Failed Database Backup" Alert at Rackspace, eventuating in a confusing ticket that took me and Rackspace several interchanges to finally deduce that "I" had caused the problem. Is there a way you could perhaps insulate a cancelled Compression Analyzer from barfing out an unnecessary "failure", please?

petey · June 30, 2012 8:39AM

The backup process comprises a read process by SQL Server to pass the backup data to SQL Backup, a compression process performed by SQL Backup, and a write process performed by SQL Backup to write the compressed data to disk.

The highest CPU utilization is obviously observed during the compression process. Both the read and write processes see very little CPU cycles used.

Perhaps the read/write process is now completing faster (e.g. due to faster disks, or to changes in SQL Backup), resulting in faster backup throughput, but also resulting in the compression process becoming 'closer' together. That could explain why your CPU-utilization level is now hitting the roof.

Is there a way you could perhaps insulate a cancelled Compression Analyzer from barfing out an unnecessary "failure", please?

Not in the way we are performing the test now. An option to avoid starting and cancelling a backup that goes through SQL Server may be to use an existing native SQL Server backup file of the database, and report the results of compressing it using the compression algorithms used by SQL Backup.

PDinCA · July 2, 2012 12:33PM

Thanks for filling out my understanding of the components etc.

I'll know in future to only run the analyzer once I've secured a Production copy and restored it to as similar a server as I can find locally.

Cheers.

Stephen

7.1 Multiple Threads question

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk