Designing a SQL server Volume and have 8 15k sas disks

So if i hope to gain lets say 10% or more improvement i should add 4 more disks add them to the raid 10 and have 6 platters rather than move to raid 5. Or based on my other question on the disk queue to you think its even worht jacking with?

1. what is the purpose of this database?
2. what's the number of users?
3. what's the application?
4. what's the size of your database?

I'd have to believe if write performance is a consideration you'd be better off sticking with raid 10.  Is it worth it?  Probably not (just a guess).  You might look at Disk bytes/sec along with the queue length.  If you have extended periods with high throughput and high queues, then it becomes a better candidate for more spindles.  But if you've got low throughput then the extra spindles won't get you much.

P.S. Nothing beats actually trying it.  If you have an afternoon, set up one configuration, restore a database, and run some representative stuff.  Then rinse and repeat with the other raid configuration.

this may sound like preaching but...

when designing hard drive for database, there are many factors one must consider. Like I said, the type of application, for example. is it transaction intensive or report intensive? meaning does it do a lot of read/write or just read? The speed of database depends more on RAM and CPU than hard drive speed, if there's not a lot of read/write. The number of users and the size of the database have a huge impact too. If you only have 10,000 lines in your 2 tables, whatever you put on there will give you more or less the same speed - you wouldn't be able to tell the difference. Database also depends a LOT on tuning.

In most cases and most applications, a RAID 5 would be fine. RAID 10 would be too fancy and to difficult to maintain

The purpose of this is a database is a tracker for phone usage it is equally as write intensive as read intensive. The systems (phones, call recorder, autodialer,etc) constantly write all day to the tune of 30,000 calls day and about 100 people running reports all day long on that data.

Its a 64 bit OS running 8gig of ram and 2 quad Xeons.  Cpu runs 2%, memory runs 99%, and IO is out of control. It has right now 8 146 15k SAS drives in raid 10 for the database and 4 300 15k sas drives in raid 10 for the transaction logs each on a different controller.

Nothing other than the database runs on that server?
Where are the db backups going?
No shares being accessed, causing disk activity?
How much of the physical memory is sql server using?
How do disk queues and bytes/sec look on the two raid sets?
And it never hurts to ask - you don't have antivirus software running on it?

And you've checked the raid software to make sure both sets are optimal and there isn't any kind of consistency check or initialization running?  Any of those things can suck the life out of drive performance.

what kind of reporting tool do you use?

instead of putting both transaction and reporting on the same machine, i would split them. you will most likely see a performance boast by putting the report on a separate database machine.

Personally, i would do RAID5E as RAID10 is too complicated with little benefit gain.

Why would you say Raid 10 is complicated?

Its raid 10 now so it wouldnt be worth changing at this point. the reporting in a combination crystal reports and a custom app for pullling recordings.

Nothing other than the database runs on that server? - no
Where are the db backups going? - I was running the sql backup (from maint menu) to the transaction log volume but now im using backup exec to a san.
No shares being accessed, causing disk activity? No
How much of the physical memory is sql server using? 6.5 of 8 GB
How do disk queues and bytes/sec look on the two raid sets? Nill on the log Volume sparatic on the database volume i have seen it at 25 :(
And it never hurts to ask - you don't have antivirus software running on it? Nope

I'm going to reindex the database and defrag the volume and see if that helps at all.

8GB is small. Setup another server and run another instance of SQL for reporting purpose only. unless your reports need up-to-minute live data. You can use SQL replication to replicate.

I would also get rid of backup exec. I don't use any of those "commercial backup" software - probably we have different need. For SQL/Oralce, i typically suggest:

1. Daily export
2. weekly Acronis Live backup via VSS (Windows 2003 and up) to SAN
3. Quarterly Acronis cold backup if you can afford the downtime; if not, forget it. 2 is enough to cover everything
4. Replication if you have a need for reporting
6. Double-take if your system is mission critical and cannot afford more than a few hours downtime.

How long do you think it would take to export a 300 gb database? IM not sure i can split the reporting or not i would have to address the auditing stuff we are doing. Anytime people run a report or listen to a recording we do an update to the audit trail and then run the select.

but you have given me a few things to think about.

I guess about <2 hours. mine is about 60GB running on a similar system Dual Quad Core and RAID5 SAS 15,000RPM. takes about 20 minutes.

Ok, if you run double-take, i wouldn't even care about daily backup. if something goes wrong, i would switch to my double-take. Daily backup is just another insurance.

Not to quibble, but I don't understand your (PaperTiger) wikipedia point about raid 10.  So if you don't replace a failed drive then your data is at risk in the event of another failure.  But the same is true of Raid 5 - the second failed drive causes complete loss of data.

And since we're covering all bases, gkaffen338, have your db people profiled the sql?  Sometimes it can be a single query killing the server, and the profiler lets you easily identify whether this is the case.

If I only get 50 poinks for all that I'd rather you deleting my post or grading it class C or below. I don't want my comments to get mixed up with someone who still recommends RAID 5. It's a compromise for low storage densities that isn't relevant with today's high capacity disks.

to counter my own point. *smile

http://www.miracleas.com/BAARF/RAID5_versus_RAID10.txt

I didn't read it thoroughly but I did catch this bit...

"One begins to get an inkling of what is going on and why I dislike RAID5,"



Or to echo mastoo... "why do you think RAID 10 is complicated?"

Andyalder,

The 50 points was to show my appreciation for your insight. The question had long been answered in the first 6 posts, and the banter between mastoo and papertiger provided me additional insight, hence the reason the question was still open.

Thanks to all...