I found these articles helpful:
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:
Variants of this question come up semi-regularly:
There are also occasional bun fights about the data/log separation "best practice".
Without more detailed analysis of what this server is doing, the same advice applies as given previously.
- RAID 1 for OS
- RAID 10 (6 disk) for data/logs/tempdb
There is rarely any point in a split with so few spindles available. A single array with a larger IOPs capacity will typically soak up the lumps and bumps of your workload better than 2 smaller arrays.
One variant that can be worth testing is putting tempdb on the OS drive. Only do so if you have a representative workload that you can replay repeatedly, to ensure a fair comparison of the configuration. If you go for this arrangement in production make sure tempdb growth is restricted so you don't inadvertently consume all free space on the OS drive.
Given that your OS drives are 7200RPM coasters, I'd be surprised if the tempdb on OS drive config bore any benefit.
Best Answer
I have posts on this same subject, but for MySQL and PostgreSQL
Feb 06, 2014
: MySQL on SSD - what are the disadvantages?Jun 25, 2013
: Postgres Write Performance on Intel S3700 SSDI hope my old posts give you insight into doing this for SQL Server