I want to take advantage of the great price/performance offered by the new Intel E5-2620 (v2) machines being offered by some dedicated server webhosts.
Unfortunately most of these E5-2620v2 dedicated server offerings are a 1CU and limited to 4 drive bays. My production SQL 2008 R2 (2GB) database is supporting an auction web application with a very high frequency of small updates by many users.
I currently have an 8 drive configuration on my SQL Server with:
– RAID 1 array (OS & Program files)
– RAID 1 array (SQL log files)
– RAID10 (SQL Data & TempDB)
My question is would I creating a disk performance issue for my OLTP SQL server if I went with:
1) A single 4 drive RAID 10 array (600 GB SAS drives with OS and all SQL Files). Would I be better off going with a much slower & more expensive machine that supports 8 drives?
2) I found one that offers 2 internal SSDs plus 4 hostswap drives, what would be the recommended disk layout. Is it still recommend to have SQL Logs on separate disk with high frequency of small db updates?
2 Drive SDD RAID 1: OS & Temp files & SQL Log file
4 Drive RAID 10: SQL DATA & System DB files, TempDB
3) Is there still a significant performance difference between SATA IIIs and SAS 15K disks as some wehosts only offer SATAs?
Many thanks in advance for any advice.
arf
Best Answer
For a 4 bay configuration with a high insert/update workload I would go with one of these:
1) Spindle only
Rationale: you will mostly be writing to the log. Latency of log is king for OLTP and it doesn't get much better than a RAID1 with a write cache. You can use SATA - which should give you at least 80MB/sec sequential writes at latency less than 1ms.
2) SSD plus spindle
Rationale: This config will favour higher tempdb load and would be good if you are skimping on DRAM (as this will cause checkpoint activity to go up and drive read I/O)
3) Pure SSD
Rationale: RAID5 with 4 x SSD is fast enough for nearly all purposes.
Everything goes on same LUN
4) 8 bay, spindle only config
Overall: always prefer a 2 spindle RAID1 with a write cache for logs. It's plenty for 99% of all cases (80MB/sec for SATA and up to 120MB/sec for SAS).