Improving Disk Performance

Posted on 2005-03-16
Medium Priority
Last Modified: 2010-08-05

I have an MS SQL DB which is relatively small - 15GB currently on an XP Prof Machine - Single HD.

This DB is not accessed by anyone and is writes approx 1million records per day (very few reads max 2000).  We would like to improve the IO Writes as it is a bottle neck at the moment.

We are considering implementing RAID on it.  Cheap and cheerful...  PCI RAID 0 with two SATA drives.   We previously performed a similar upgrade (RAID 0+1) with very good success on another machine, but we used expensive Maxtor Raptor Drives.

This is not a critical process at all (hence RAID 0 - with backup to IDE).  So we are looking for a cheap SATA Drive to put on.  Our thinking is:

- RAID PCI Controller
- SATA 40 GB x 2 Drives  (GBP 40 each)

Our concern is that this setup is going to cost us GBP 100 and give us 80GB.  But we only will use 15GB and have concerns that with such a low volume we may be better off purchasing 1 single high performance drive instead.

So my question is:  Purchase 1 high performance drive or implement RAID??? The costs are about the same.

(remember.. DB is only 15GB).


P.S I am aware of the draw backs of RAID 0.

Question by:amacfarl
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Expert Comment

ID: 13553837
To me RAID 0 is the best you can get, unless you go buy a SCSI drive (But that's more expensive)
You should also think about the possibility that your DB might increase in size.
So to not waste that space, you could use the extra Gb for rarely used data.
In any case, we're talking about a solution that'd cost less than $150 (purchase of 2 disks and a controller card)

Author Comment

ID: 13554029
thanks for your prompt response.

overall the DB will not grow much as the DB is purely used as a point to capture streamed data.  Only a daily basis it uploads to another system and deletes entries.

if the DB is only 16GB and the HD space is 80GB...(40GB x 2) will the DB be equally distributed across both HDs??

Does the size of the HDs have any impact on the distribution of the data??
LVL 16

Assisted Solution

samccarthy earned 1000 total points
ID: 13554549
In a RAID 0 the data is striped equally across the drives.  Always buy for expansion, if you don't you may regret it later.  Let me ask, how critical is it??  If you can live with a drive crash, have limited funds and are going IDE, then go with 1 high performance drive.   If you cannot live with a drive crash, then go with the 2 mid performance drives and make them a RAID 1.

If we go with the first comment I made, lets look at it.  Single High Perfomance Drive or Raid 0 on 2 medium performance drives....
A single failure puts both out of commission, but the RAID 0 has 2 points of failure.

While Raid 0, Striping accross 2 drives writes faster than a single drive of the same specifications, it may not write any faster than a single high performance drive running at a higher RPM with a bigger buffer and designed for higher data transfer rates internally.

If I were doing this and going by what you describe above.......  If it was not critical and you could live with a failure, I would get the 1 high performance drive.

Accepted Solution

jltari earned 1000 total points
ID: 13554825
>>I would get the 1 high performance drive
If you'd allow me, _I_ wouldn't :)
It all depends on what we're talking about when we say "high performance".
If you go to next door computer store, you can get 7200 RPM drives for the best price per Gb. So, my question is : What's better than that?
Looking down to hard drives' specifications, you'll find that some may be better than some others, but that won't be much of a difference.
On the other hand, using RAID 0 (or RAID 1+0 if you want safety) can speed the reading / writing process by almost splitting the access time by two.

I do agree with samccarthy about the risk of failure, though : You do have twice the chance of failure with 2 disks rather than with just 1.
I'd go with RAID 1+0 for this reason (I personally use RAID 1 at home)
LVL 16

Expert Comment

ID: 13555286
I use RAID 1 at home too.  When I speak of High Performance drive, I'm talking about the 10k drives as most IDE's now are 7200 and many of the higher end have the 8 or 16mb caches on them compared to "Normal" or "Mid Range" with only 2mb caches.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I previously wrote an article addressing the use of UBCD4WIN and SARDU. All are great, but I have always been an advocate of SARDU. Recently it was suggested that I go back and take a look at Easy2Boot in comparison.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This video teaches viewers how to encrypt an external drive that requires a password to read and edit the drive. All tasks are done in Disk Utility. Plug in the external drive you wish to encrypt: Make sure all previous data on the drive has been …
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
Suggested Courses
Course of the Month10 days, 4 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question