I’m looking to upgrade one of my site servers. The site currently executes around 20m sql queries a day and this is growing quite fast. Right now IO throughput is a real issue, so my idea is to move various databases onto their own disk pairs. My idea is to do something like this:


Disk 1 & Disk 2 / Raid 5,6 or 10 (SAS or SSD) - database 1

Disk 3 & Disk 4 / Raid 5,6 or 10 (SAS or SSD) - database 2

Disk 5 & Disk 6 / Raid 5,6 or 10 (SAS or SSD) - database 3

Disk 7 (SSD) - Transaction Logs

Disk 8 (SSD) - Transaction Logs

My experience is lacking here, so got a few questions:

1) Firstly, does the above look stupid?

2) Is there anything I should look out for in the RAID controller, eg. Bandwidth throughput when using SSD?

3) Are all SSD disks created equal? I’ve picked up threads saying that certain types are substandard?

4) I’m assuming that for simple two disk mirrors any of those RAID levels will be ok? Bad assumption?

5) Any other tweaks to make this config better?

My site is highly transactional, but the databases aren’t that big, only a couple of gigs currently. So my major scaling requirement is IO, not size.. I’ll be throwing a lot of RAM at the box as well to put more indexes in memory etc..

Thanks Dave

| improve this question | | | | |
  • What are your io requirements? Are your databases mostly read or write? How large is your database? How much ram do you have? What is your page live expectancy? – mrdenny Apr 24 '11 at 20:19
  • Hi mrdenny, I can provide all that info but I didn't want to increase the scope of the question too much. 128Gb RAM, all indexes fully in RAM. More Read than Write (about 10:1). I'm guessing with the higher read RAID 10 will be better as reads will be distributed..? – Dave Sumter Apr 25 '11 at 8:23
  • With all the data in memory already why are you trying to increase the throughput on the disks? You shouldn't be hitting the disks very hard if at all. What's the Page Life Expectancy perfmon counter show? What's the current seconds per read and seconds per write perfmon counters show? – mrdenny Apr 25 '11 at 16:16
  • Hi, all writes will hit the disk, only reads can be serviced entirely from memory. The app is highly transactional, hence lot's of reads and writes.. – Dave Sumter Apr 25 '11 at 17:23
  • Writes are written the transaction log, then flushed to disk upon checkpoint. Writes aren't written to the data files as they come in. – mrdenny Apr 25 '11 at 19:12

No, all SSD's are not equal. The Intel G2 are quite good, and the Crucial RealSSD are good. However, the best performance is from the SSD cards that plug into PCI Express slots. The best of those currently have 1.4 TB/second (sequential) throughput.

The common denominator is random write throughput. This is best measured by running the Microsoft SQLIO utility.

You may want to go even further and put (some) indexes on a separate file group that are on an SSD. This may not necessarily help update performance, but would help query performance. Some queries may never hit the database, only the index(es).

Don't forget TempDB, if you have procedures or queries that use that.

| improve this answer | | | | |
  • Thanks Greg, that's a great suggestion..! I've never thought of putting indexes on to their own SSD disks. That would be great for some of the indexes that get updated a lot.. – Dave Sumter Apr 24 '11 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.