6

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

migrated from stackoverflow.com Dec 20 '13 at 22:26

This question came from our site for professional and enthusiast programmers.

1

For a 4 bay configuration with a high insert/update workload I would go with one of these:

1) Spindle only

  • One RAID1 for the log. Back this up with a a few hundred MB of write cache
  • One RAID1 for the data files and tempdb

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

  • One RAID1 for log with same configuration as the above.
  • One RAID1 for data files and tempdb

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

  • Put them in a RAID5 if you are not worried about wearing them out.
  • If you are using consumer grade SSD - put them in a RAID10.

Rationale: RAID5 with 4 x SSD is fast enough for nearly all purposes.

Everything goes on same LUN

4) 8 bay, spindle only config

  • Put 2 disks in RAID1 for the log file.
  • Rest goes into a RAID10 or RAID5 (RAID10 if you are worried about tempdb)

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).

Your Answer

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