Sql-server – SSD for production database

I found these articles helpful:

• Why RAID10 (0+1) superiority is a myth (specifically versus RAID1).
• And this is an additional guide on raid level comparison.

Further reading suggests that the benefits of using RAID1 for log files (assuming isolated from data files) is lost when more than 1 log file exists on that RAID1 array. This is due to the sequential nature of the transaction log writes to disk. The sequential write benefit is lost when multiple log files are accesses on the same RAID1 array due to the random nature of access on a spinning disk. This would suggest that RAID10 is the better choice in a multiple DB environment unless you have the disks to isolate each log file.

These stats below sold me on proposal 3, isolating tempDB on RAID1 by stealing 2 disks from LOG array moving log array from RAID10 to RAID1. Basing much of this on RAID1s ability to maintain good WRITE speed.

TEMPDB is clearly under more stress than I have realised.

These table rankings ring true for snapshot values during normal operation (not just the accumulated totals) as we do have intensive out of hour routines.

TOTAL IO:

db.tempdb.mdf = 144,747,290,352

db.2.mdf =         100,482,243,080

db.2.ldf =           2,571,065,773

db.s.mdf =         1,702,508,040

db.s.ldf =           223,032,162

TOTAL READS:

DB.2.mdf =         84,851,614,280.00

db.tempdb.mdf = 72,271,813,552.00

db.s.mdf =         1,691,504,864.00

db.2.LDF=          93,822,304.00

TOTAL WRITES:

db.tempdb.mdf = 72,475,476,800

db.2.mdf =         15,630,628,800

db.2.ldf =           2,477,243,469

db.tempdb.ldf =   222,946,079

One possible concern maybe the additional of the tempdb ldf and mdf on the same raid1 array but if this is a problem tempdb.ldf can be moved to the log array.

These are helpful links that explain TEMPDB usage, what it does and how it may effect my apps:

• http://bradmcgehee.com/wp-content/uploads/presentations/Optimizing%20tempdb%20Performance_SQLCONN.pdf
• http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

With spinning-platter disks you want to have the logs and data on separate drives as random access data disrupts the sequential log write operations, making the logs a performance bottleneck. SSDs do not have this issue as they lack the performance constraints imposed by the mechanical action of conventional hard disks.

If you're getting SSDs for a DB server, get ones designed for a long life span, such as Intel S3700s. You're probably better off getting two of those than four cheaper ones, and 2x100GB units + a hot spare (which is probably enough to keep you going for a few years at your current rate of growth) should cost around £200 each according to Google Shopping.

Mixing logs and data on the same disks isn't such a big deal any more. However, you may get some resiliency out of having separate log drives. If you want to do this then 5 disks (enough for two pairs + a hot spare) should still see you with change out of £1,000.

Try two good quality drives in a RAID-1, with a hot spare for a starter. Your growth suggests you probably won't have transaction volumes so high that this setup can't keep up with your application workload.

Back up your database onto spinning disks - a couple of enterprise-grade SATA disks and a hot spare should cost you a few hundred dollars.

If (and only if) you have performance problems with that lot then you might look into adding more SSDs, but I suspect just one pair will be fast enough to keep up with your transaction volumes quite comfortably.