October 15, 2006 at 1:04 pm
All,
I am running a massive set of stored procedures on a big SQL server - they are for a simulation application and take over 40 minutes to run often.
The spec of the server is Windows Enterprise edition, SQL enterprise edition, 8gb ram, 8 dual core processors.
When the simulation runs, my CPU average never goes over about 7-8%. I need to increase the performance dramatically, and before I look at the code, I would like to see the job taking a lot more of the processing power of the server.
Anyone have any ideas what I can do to increase the amount of processor my SQL job is using?
TIA
Tom
October 16, 2006 at 2:42 am
Quite possibly you have a bottleneck elsewhere in your application that is preventing the CPU breaking 7%. Optimisation is an ongoing exercise in (re)moving bottlenecks...you solve one problem, you move onto the next...
Can you post some of the SQL and related execution plans?
October 16, 2006 at 6:49 am
You have 16 cpu cores. I'm not sure how things get scheduled on dual-core CPUs, but I would guess that they behave much like 2 single core CPUs. Your 7% figure is just about right for 1 CPU out of 16, running at 100%. (100% / 16 = 6.25%) If your workload does not get handled by any parallel execution plans, then you might expect this maximum level of CPU utilization, regardless of any other bottlenecks.
Now, I'm off to figure out how I can make the business case for a box like that....
jg
October 16, 2006 at 7:29 am
Something I see far too often when people design a system to host a database server is that they focus on how many processors and how much RAM the system has while spending almost no time thinking about what is often the cause of bottlenecks in systems hosting database servers, the disk subsystem. You didn't specify what kind of disk subsystem your server has so we there's no way to tell if that could be a possible bottleneck.
My guess would be the bottleneck has to do with the disk subsystem. It's possible you simply don't have enough spindles or you aren't taking advantage of the spindles you have. It could also be possible that you don't have appropriate indexes on your tables. This might sound rather primitive to some but you can get some evidence as to whether or not your disk subsystem is part of the problem simply by looking at the server while the process is running, assuming you have physical access to the server. If you observe that the disk lights remain on a great percentage of the time while the process is running that is an indication that the disk is being heavily used while the process is running of course that's not the best way to determine if the disk subsystem is the bottleneck. The right way to figure out what the bottleneck is, is to start with the execution plans that are being used by the queries that are part of your process.
October 16, 2006 at 8:40 am
Tom,
As mentioned by others in this thread, your problem will turn out to be a combination of issues which when addressed one by one will provide a cumulative solution to your performance issues.
This type of tuning is not something that can really be done by dialogs on a forum, rather it will take a planned, interactive effort by someone who has done it before. I have just completed an investigation and preliminary upgrade to an application that was really dying, and the immediate result was a huge improvement. I hope I'm not disregarding this forum's etiquette, but if you'd like to contact me independantly, I'd be happy to talk about this further.
Regards,
Rob Marmion
October 17, 2006 at 6:45 am
Try running this sometime when there is no other load on your server
use [master]
go
select *
from
sysobjects so1,
sysobjects so2,
sysobjects so3,
sysobjects so4
option (maxdop 1)
This query should be (almost) all CPU and little disk IO. What kind of CPU load do you get?
October 17, 2006 at 5:21 pm
Jeff Gray hit it... if you are looking at cpu usage on task manager, you are seeing the just isn't clear. You don't say what CPUs you're running, I presume they are not 64 bit. Have you turned on Address Windowing Extensions (AWE)? You're posting in the 7/2000 forum, I presume you are running 2000. What SP are you at? If you are at SP 4 with AWE turned on and you have not installed the appropriate hotfix from MS, you are not using all of your memory - even with AWE turned on. You might try looking at performance monitor, there are some pretty neat DB things to monitor there. I find PM a lot easier to read if I switch it to "report" vs. graph (which it comes up in)... guess that depends on what you monitor; but there are dozens of agents there, lots to look at.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply