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
If you have a single RAID10 volume then as far as SQL Server is concerned you have one volume and you can't control how things are stored, splitting things into extra files unnecessarily will likely have detrimental effects as it would on a single disk.
If you wish to try gain performance benefits from segregating data between spindles then you need to split the drives into separate RAID arrays, perhaps four in R10 for data and two in R1 for logs, or three R1s for everything, data and logs spread over the three.
Unfortunately there is no fixed wisdom here, it will vary greatly depending on your application's I/O loads. Splitting data and logs makes little difference for many load patterns (though can make a massive difference for certain write heavy loads) for instance. The only general advice that is applicable without a lot more detail about the application is "bung it all on that volume on one file/data and one/logs and trust the I/O scheduler to be fairly bright".