May 2, 2007 at 7:56 pm
Hello,
This is a repost of a long post that eaten by the forum software.
I'll make it short this time...
We're trying to increase the performance of a SQL2K server.
The main db is roughly 5.5GB, OLTP, running on X86 32bit, 8Gig Ram, 2x Xeon Dual Core (DC) 3.0Ghz - 4 total cores,
2Gbs FC SAN w/ multi discrete filegroups on independent IO/RAIDs for Db, Indexes, TLogs, etc.
Compared to when the Db was <2Gigs, performance is now slow and the dB is contention prone.
We've tuned performance from a logical point of view (indexes, Hints, Application SQL, etc) as much as we can, and added
more spindles for I/O.
But, increases in performance at this point are marginal. CPU utilization < 50%, buffers OK and disk IO is usually < 1000 IOPS
So, we decided to upgrade to the latest & greatest in mid-range servers - Intel's Xeon 5345 Quad Core (QC) platform.
We chose hardware based on this:
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=106111301
http://www.intel.com/performance/server/xeon/database.htm
Basically, we went with the HP DL360 G5 16Gig Ram, 2x Xeon Quad Core (QC) 2.66 Ghz - 8 total cores, 4Gbs FC SAN.
We're even evaluating SSDs and RAM disks.
Long story short, under simulated loads, the QC server is running queries at about 3 times slower than the performance
of the DC server. CPU utilization < 5%, buffers OK and disk IO < 500 IOPS.
With other testing tools (IOMeter, SQLSimIO, etc), we've been able to establish the hardware is capable of much more than
this. For example, IOMeter maxes out at 100,000 IOPS on the RAM disk and 3000 IOPS on the FC SAN.
I knew that the clock speed difference between the two platforms might make a difference, but I did not expect this!
We've tried different settings WRT Thread Fibers, parallelism, CPU affinity and AWE, all yield similar result.
However, here's the mystery, with all but one of the 8 cores of the QC server turned off for SQL2K usage,
the performance of the new server is roughly at about 230% of the DC server.
CPU utilization gets to < 30%, buffers OK and disk IO ~ 1500 IOPS.
This is not ideal, as I would like to have the extra cores (CPUs) under heavier load conditions.
Does any one have any insight into why MSSQL 2000 is behaving in this manner?
May 3, 2007 at 10:53 am
I do quite a bit of this and there will not be a golden fix .
With more memory than database you will have eliminated disk issues except for tempdb ( largely ) make sure your tlog drives are fast, even raid 10.
As you've improved performance by limiting cpu, have you set the number of procs used by parallel queries in ent manager?
If you have HT on your box, disable it btw. Try adjusting the number of cpus, I'd try 4 first, than 2 than 1 running the same tests. I'd imagine you're probably sufferring from parallelism which may in certain circumstances cause thread starvation. Even if your database is in ram paralleism wills till cause issues by causing excessive page io. It's a fairly classic case where introducing more procs/core to gain performance actually degrades it!! I've seen this on many occassions.
To see if this is your problem collect worst performing queries, get a trial of Idera diagnostic manager which will do this for you, paste the sql in QA and check for parallelism, using profiler capture the io, then add a maxdop 1 statement and run again and capture the io again - you may find you need better indexes of course - excessive scans can be very expensive e.g. a select * will almost always parallel scan, on 1 core it may do say 100k io, on 16 cores it may well generate 1,600k io , 100k for each core - I'd suggest you look into this.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 3, 2007 at 1:25 pm
try turning off parallelism or force it to 1 CPU
otherwise how is your IO? The TCP benchmarks are split across dozens of spindles and no one ever does it like this in the real world.
we just bought a few dl 380's with the quad core CPU's and the performance is mind boggling, especially IO. in production on a DL 760 with 8 CPU's, 8GB of RAM and an EMC Symmetrix it took around 2 hours to install SQL 2005 and SP1 off the SAN drive. on the DL 380 around 20 minutes for both. in both cases it's 8GB RAM and Windows 2000.
May 9, 2007 at 2:27 pm
I just got finished reading a white paper from Quest Software: http://www.quest.com/landing/?ID=979
I read this thread with a great deal of interest. I will assume all indexes & stats were rebuilt prior to your testing.... I know this sounds obvious but one never knows.
Kurt Zimmerman
DBA
RHWI
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
May 10, 2007 at 10:59 am
i was going to reply to your last email, but it's been crazy here
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply