RAID5 problem

We are a company using Cache for Windows (x86-64) 2007.1.3 (Build 607) and have been experiencing slow database performance, especially when reading, due to the large database size of 19Gb.  We're using a custom made application build with the .NET framework 2 via an ODBC, and noticed that when making queries through particularly large tables, even though having proper indices where applicable,  the performance is quite lacking.

For example to make a select count query on a table having 23,267,429 records it took 237 seconds, 152 sec, 172 sec on three different occasions (e.g. SELECT COUNT(ID) FROM MY_TABLE). Is that "normal" or can we make it in 10 seconds or less?
On the same table if we had to run another query using an index built on two fields, and if we tried to look for a non-existing record, it would take 1.3 sec.
If using the same table if we had to run another query using this same index, and if we tried to look for an existing record, it would take 1.1 sec.
On a relatively smaller table it would take 0.35 seconds

The first question is whether there is a way to make the database reading possibly faster . We have tried playing around with the memory by allocating more RAM but without any positive results.  Using the server Microsoft's performance montior, it is evident that during peak load there are very high percentage reads on the disks going on whilst the CPU is relaxed and the writing is minimum.  There are times when the server reading is fixed at 100% usage for over half an hour.

Secondly, as part of the solution, we are considering changing the RAID5 to RAID10 with four disks of 300G SAS running 15000rpm. Will that help to improve the performance? If yes, what cluster size of disks and what other technical setup do you recommend please to improve, hopefully in a drastic manner the performance?

Further details about our scenario
x3400, 2 CPUs Xeon Quad Core @ 1.60GHz
8 Gigs RAM
RAID 5 setup with five 146.8GB 3.5in 15K HS U320 SAS HDD
1 Gigabit Ethernet
Windows 2003 server, having hardware dedicate to this database only.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

rmmustafaConnect With a Mentor Commented:
It is obvious that your application and search quires depends on the HD activity , so moving to RAID 0 will surely solve some of the issues on this part , I tested RAID 5 and 0 disk performance myself and was astonished with the results. On RAID 5 array (5 SCSI Hard drives with 132GB ) doing normal partition backup for around 10GB of data , the transfer rate was 8 MB/s ,,,, I tested same array on same physical server using RAID 0 and the transfer rate was 30 MB/s ,,, and this small test can show the difference in throughout between the two RAIDs implementation .
Back to your case , as long as the quires are run directly on the disks and there is no indexing mechanism to populate the dbase  index into RAM , then adding more  RAM , or upgrading the CPU  wont help a lot.
Depending on where the bottleneck is, moving to RAID 10 could help.  We have a SQL server implementation here that is quite large and as it grew larger accessing it became slower and slower.  We hired a SQl consultant and he made several recommendations.  Upgrading the firmware of our servers was one thing, but the one that really made the difference was moving from RAID 5 to RAID 10.  My supposition is that you would get a speedup from doing the same, but only if that's the bottleneck and the only way to tell that is get someone looking at the server who knows what to look at.
Moving from RAID 5 to RAID 10 will only help if the controller does load balanced reads across both disks in a mirror. You don't say what it is. Generally the more disks you have the faster you can read (and write) so you can add more and short-stroke them by partitioning and "wasting" most of the space.

With only 19GB database you might be better off putting it on SSD. A fusion io drive might cost $3000 but it'll certainly give you much faster access time.

SQL people will probably tell you to split the table into several smaller ones.
I don't know that anyone of us completely answered the question since the asker never responded to any possible solution, but I believe that we all contributed correct information.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.