i am experiencing SQl write performance problems on a very shiny server. Got data files on a Raid 1+0, log files on a separate drive, all SCSI, Win2003 server, 6G RAM, 2 Xeon processors. I've created a small benchmarking program and run it on my desktop pc and this 'big' server. Here are the results:
Desktop: SQL server inserts: 78 Seconds, Direct writes to the harddisk(Just write a string to the file 10000 times): 13 seconds
SQLServer: SQL server inserts: 422 Seconds, Direct writes to the harddisk: 16 seconds
So, for some reason, my 'shiny' machine is 6 times slower on writes than my desktop. When i tried comparing the select performance, my shiny server is 10 times faster than my desktop.
Initially i had Raid5 on my server and it had poorer direct write performance but now, direct writes seem to be ok, so, i recon this is a problem related to SQL server.
What can i do to improve the insert performance?
Thanks in advanceYour performance should double by placing the database file and transaction log file on different drives.
HTH|||As i mentioned previously, i already have my data and log files in different physical drives but it it does not make a difference.
Playing around with Raid configurations i managed to significally improve the speed of direct writes to hardisk (not within SQL) but sql write speed still stayed the same. I have a feeling that this is either Win2003 server or sql server matter. Cannot find any info about it.
Had someone come accross the same problem?|||Maybe check if your RAID-controller has its writecache disabled. How did you test? Within Queryanalyzer or custom Tool.?|||I wrote a small c# utility to do the test. Tool does 2 types of test:
1. It runs 10,000 insert quieries against a simple table
2. It writes a string into the flat file using direct file access 10,000 times.
I thought that writecache might be an issue but my direct writes (test 2) perform very well (equal to my desktop pc, whereas test 1 is 6 times slower than on my desktop) so i think that bottleneck is not in disk/raid configuration but somewhere else.
Not sure where though:( Same thing happens with SQL7.
Currently trying to install Win2k server op.|||For anyone having the same kind of a problem, issue was resolved by by installing a battery backed write cache on the SCSI controller. Achieved 24 times speed boost at once.|||... though enabling write cache is not a good idea anyways since it can lead to data corruption, even during normal operation.|||I've paid £250 for this write cache chip, it's backup onboard battery will last for 72 hours. I am pretty confident that my cached data will be save. Besides that, apparently write caching with battery backup is microsoft's recommendation. (Q230785)|||It's not a matter of how long your battery lasts. I read that the datapages can get out of sync with write caching enabled which leads to corrupt data files. But not 100% sure could be an issue of SQL 7 only.
... maybe one of our friendly MVP-SQL-Gurus here can confirm or disproof that hardware write caching can be a problem? :)
- Moon
