October 27, 2009 at 6:45 am
Hi all,
Not sure if this is the right room to post on but still hoping to get some answers to a performance issue I'm seeing.
I'm not a DBA of any variety, I'm a performance tester and i've been running tests against a new application that's going to supersede the old one. As a result the new system has to run against the existing architecture:
- SQL server 2000 set up to hotfix 8.0.818 (or so I'm told).
- Server with 2xdual core @ 2.4Ghz and 1280Mb RAM.
As you'll all know, when monitoring a process using perfmon the total processor available is 100% times the number of cores on the machine. So, for system under test for me this is 400%.
Now, the problem:
Throughout the duration of the test, the %processor usage by the sqlservr process does not exceed 100% and the %idle is at 300%. My first thought was that this was not multi-threading and it was doing all the work on a single CPU core.
However, to complicate matters, I've recorded the individual %processor time that each core is doing - and none of them are maxed out! The highest average out of all 4 cores is only 40%.
From what i can see, it looks like SQL is spreading the load across all 4 cores but then doesn't exceed the capabilities of just one core.
I've done some research and i've seen that there are some versions of SQL that have single processor licenses. From what i can tell, we don't have this for 2 reasons:
A) These are per socket not per core - in which case it would be doing the work on 2 cores and it'd be able to get to double what I'm seeing
B) All the work would be on just 1 of the cores and that would be reflected in the performance... but it's not... I'm seeing it being spread across all 4 cores.
Any one have any ideas or suggestions?
Cheers,
Rich
October 27, 2009 at 7:23 am
It's very hard to max out all of the CPUs on modern multi-proc machines. Very few workloads are limited by CPU bottlenecks. Far more common are memory/disk. It's very likely that SQL is waiting for disk or network or locks or similar and hence the CPUs aren't all fully busy.
DBCC SQLPERF(WaitStats) will tell you the relative prevalence of wait types, also check sys.processes, see if queries are waiting and if so, for what.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2009 at 9:01 am
It does look to be the disks!
I've run some SQL the trash them and exactly the same results are created.
Thanks 🙂
October 27, 2009 at 9:27 am
Not surprised. From experience badly-written SQL will max the processors out but once the code's optimised the disks are usually the bottleneck.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2009 at 9:53 am
Rich
if i remember those machines (5664-server is it?) then they only have 1 disk (ok 2 disks in raid 1) on them???
1 disk and 1 Gb of memory will almost never be able to use that much cpu
Given that you have a couple of hundred of them and they are fairly low spec in terms of drive bays , then i guess you aren't going to be able to use 2x2 core just for sql. - even worse - if the system starts paging then you'll see even worse performance.
MV
MVDBA
October 28, 2009 at 4:05 am
Hey Mike,
That's the exact server 🙂
and cheers for the SQL loop you sent over to thrash the disks.
All of the new records from my test are hitting 1 table. I'm expected values of approx ~7500 on a peak hour for the pilot site (ram[ing up to 21,000 for the biggest site on the estate).
The size of the table at the end of testing (over a 3 day weekend test) came to >550,000. Do you have any suggestions or comments on the level of suggested house keeping? ... if it would have much effect, that is!
I've been told the inserts are only a few bytes so the table will be huge before it starts to feel the pinch.
Rich
October 29, 2009 at 9:36 am
Just to add a note about the load being spread over all the CPUs--that's pretty much normal. When you have a single thread running at 100% CPU the Windows task scheduler won't always schedule that thread on the same CPU (unless you've set the processor affinity for the thread), so you'll often see a situation where what WOULD be a 100% single-CPU load gets shared out among all the available cores. Nothing to do with SQL server, just Windows working in mysterious ways as usual!
October 29, 2009 at 12:36 pm
On another note - you really, really need to upgrade SQL 2000 Server to at least SP4.
You are presently running SP3a with security hotfix 818 - which has not been supported by MS since June or July of 2007 if my memory serves correctly. By upgrading to SP4 you'll be 'supportable by MS until sometime in 2013 !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 29, 2009 at 12:46 pm
rudy komacsar - a.k.a. Doctor "X" (10/29/2009)
By upgrading to SP4 you'll be 'supportable by MS until sometime in 2013 !
'supportable' meaning if you have a premier support agreement with MS and you've paid for extended support.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply