September 26, 2003 at 3:42 am
Hi community,
today I have a weiered problem with a new SQL2000 installation. I have a stored proc which calls another stored proc which converts a char field with an AS400 date format to sql2000 datetime. In our developer environment it took about 15 min for 900.000 rows to do the job. On test machine the processor takes about 100% of CPU. In production it takes about 80 min and SQL2000 takes only 2-3 % of CPU. SQL2000 priority is set to high on both machines.
I've found out with Performance Monitor / SQLServer Access Method /full scans/sec that my testmachine makes about 1000 full scans / sec, my production server only 160 full scans / sec. I checked following hardware benchmarks with Dr. Hardware an AIDA.
DISK I/O prod better than test
RAM read prod better than test
RAM write prod better than test
I don't know why my production machine is 6 time slower as my test machine. For test I've deactivated hyperthreading on prod, but nothing happens.
Following the hard- and software configuration of test an prod environment:
Test machine:
W2K SP4, SQL2000 Dev.
P4 2 Ghz, 1 GB Ram (500 MB reserved for SQL2000), ATA 100 IDE device
Prod machine:
W2K SP4, SQL2000 Dev.
IBM x235 Server
2 x P4 Xeon, 2,5 GB ram (1,5 GB reserved for SQL2000), SCSI RAID1
Any ideas where I have to look for?
Every comment to solve my problem is welcome.
TIA
Volker
Edited by - v_schoen on 09/26/2003 08:14:05 AM
September 29, 2003 at 8:00 am
This was removed by the editor as SPAM
September 29, 2003 at 8:49 am
Try turning off parallelism on the production server.
--Jonathan
--Jonathan
October 1, 2003 at 8:08 am
Hi community,
after 2 days of analyzing my server, I found out, that the harddisks are the bottleneck. Normal harddisk tests with dr. hardware or Sandra show a really good performance, but with filemon an taskmanager (i/o writes) I found out, that the writes to database log are the bottleneck. Now I'm in contact with my hardware vendor.
regards
Volker
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply