-1

When running a job on a SQL Server 2008, I notice that 2 CPUs are running at 100% but 2 are only about 30%.

How can I spread the load and will it reduce the duration of the job which currently takes over 1 hour?

It's running on a Windows Server 2008 -VmWare with SAN. 32 bit. ~3.5GB memory.

If you need more info please ask.

Bryan
  • 7,628
  • 15
  • 69
  • 94
Charlie
  • 41
  • 4
  • What CPU do you have? – David Schwartz Nov 16 '12 at 00:29
  • 1
    The scheduler is almost certainly better at this than you are - it was designed by people who spend their entire professional job thinking aout how to cram as many processes through the CPU as possible in the shortest amount of time. Given that, can you tell us why you want to muck about with your OS scheduler, and possibly create a performance problem? – voretaq7 Nov 16 '12 at 02:27
  • "Almost Certainly" made the rest of your statement redundant. But I get what you are trying to say. Thanks, – Charlie Nov 16 '12 at 15:02

1 Answers1

4

Without modifying your job so that more work can be done in parallel, you will likely see worse performance if you manage to even out the load. The two CPUs which are at 100% are likely dedicated to two parallel streams, and moving them around will cause additional overhead for context switching and loading CPU memory caches. The other two may be sharing additional load which may be bottle-necked by the first two processes.

If you can increase the degree to which the job can run in parallel you might be able to shave time, but it should still take over half an hour. (current runtime * 260/400 should be your best case improvement.)

Bottle-necks are common in cases like this. If you solve one bottle-neck, you will likely discover another.

Some database problems can be solved by modifying the queries involved. Given that CPU appears to be the bottleneck, it may be possible to improve the database code. This would require tuning the query using the most CPU. Profiling the application may yield information which would help in optimizing it.

BillThor
  • 27,737
  • 3
  • 37
  • 69