April 4, 2003 at 5:22 am
I have SQL 7 running on a dual processor pc. The issue being when the server is busy, the total CPU usage rarely goes above 50% Both processors are being used but tend to even each other out e.g. one will hover around 80% and the other stays around 20%
I would have thought that when the server is busy, both processors would ramp up to their max values?
Any help appreciated
cheers
April 4, 2003 at 2:03 pm
I've not seen this. Is there an affinity set? Check sp_configure for an affinity mask. In general, I've seen both CPUs run at similar levels, though I guess if one guy had a hairy query, it might spike one CPU while the others run lower.
Steve Jones
April 4, 2003 at 2:36 pm
We have 4 processor and most of the time the first one is giving top pics while the others are still low.
But the overall cpu usage remains low. It don't think this is a problem.
April 7, 2003 at 3:55 am
Could also be that you are suffering from a Memory bottleneck. Look at task manager and see how much memory is in use/paged and if there is a high number of page faults. If CPU is only around 50% average then I would check memory.
April 7, 2003 at 5:59 am
No affinity mask is set. Memory seems fine, 1.5GB - my SQL Buffer Cache Hit Ratio is consistently above 99.9% and pages/sec is very low, averaging about 2 over any 24hr period.
I'm pretty sure it's our reporting tool that hammers the performance, although I can't find any memory counter spikes to go with the corresponding processor spikes.
Thanks
April 13, 2003 at 6:53 pm
You might want to look check the Properties of the SQL Server in EM. You might find some info in the Processor page. In Parallelism section of the page, I guess changing the "Minimum Query Plan threshold for considering queries for parallel execution" field might change the way both the processors being HIT.
Please try and let us know. I am not sure about this.
Hope this Helps!!!
Regards,
Murali Damera.
.
April 17, 2003 at 8:27 am
We have seen this -- like a mirror of each other -- when the stepping is different on the CPU. We had to replace one of the CPUs to exactly match the other and it went away. In the meantime, rebooting cleared it up temporarily. When we were under CPU pressure, it caused a bottleneck. It's like the two were acting as a total of 100% CPU instead of 200% CPU. Hope this makes sense.
April 17, 2003 at 4:15 pm
I've had a similar issue but it turned out that the problem was the application generating the load.
If the reporting tool is generating queries one at a time sequentially and those querries cannot be split between the procs, this would explain the behavor you are seeing and it is what you would expect to see.
You could confirm this by setting up two or three clients that are making requests concurrently. If you do not have a load generator, have you and a buddy hitting an asp page that runs a query that is designed to be processor intensive and run the reporting tool at the same time. I suspect that you'll see the CPU Utilization top out.
April 22, 2003 at 7:37 am
I had the same problem, and I solved it just by setting "NT Fibers" on.
I have an SQL 7.0 DEDICATED Server with 2Gb RAM and 2x600Mhz processor.
Alexandre Aschenbach
Alexandre Aschenbach
April 24, 2003 at 8:36 am
We have a development SQL box with 4 500s. It will start loading CPU#4 first, then 2, 3, then 1. But since we never fully tax out the machine, I’ve never seen all CPUs loaded at once. Our production SQL Server is 2 700 Zeons. These 2 CPU’s have always shared the exact same load, no matter the condition. They run at an average of about 70% at all times, we’re adding 2 more this weekend to help out. I’ll watch to make sure the stay level with each other. On both machines I have set the boost priority check box, and NOT the use NT fibers one. I remember reading somewhere that was bad to use NT fibers, but I can’t remember why now, something about stability with W2K. We also use 4 gigs of ram on each box. We lost a 2 gig pack on the production server and the CPU usage went through the roof, once we replaced the 2 gig pack it dropped back down.
- Vega
April 24, 2003 at 10:04 am
Ok guys. I have a few SQL2000 servers with DUAL CPUs, none of them give me the issues discussed above.
Here is the configuration I have on EM.
http://www.appliedi.net/carlos/sql.htm
Hope this helps.
-Carlos
April 24, 2003 at 10:24 am
Some motherboards send interrupt requests to one CPU only. This can cause this issue.
Michael R. Schmidt
Developer
April 24, 2003 at 10:33 am
MikeyMikey brought up a good point. However, if that was the case Windows would only see one CPU as well.
-Carlos
April 24, 2003 at 12:39 pm
Most of the valid causes have been penciled down. Donno if the queries written use this OPTION (MAXDOP 1), which forces the query to use one cpu only.
May 1, 2003 at 7:22 pm
I saw this once on a Dell 2 CPU server. The situation degraded over time until we determined that the lagging CPU was defective. It was replaced and CPU usage evened out.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply