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:
I personally have used Raxco PerfectDisk (not associated with the company or any of their employees in any way) to do online defrags of SQL Server LUNs. Works perfectly, if it does slow the server down a bit. I would recommend doing it during periods of lighter activity. When I say "works perfectly" I am referring to it not corrupting the volume or the SQL data files.
The built-in defragmenter does a very poor job if certain structures are fragmented on the drive. PerfectDisk shows you details about all the items that are fragmented including the NTFS allocation tables, directories, alternate file streams, etc. etc.
Does PerfectDisk defragment SQL databases? http://support.raxco.com/KB/a106/does-perfectdisk-defragment-sql-databases.aspx
See this archive copy of the Technet News Magazine regarding SQL Server and fragmentation: http://web.archive.org/web/20100803204458/http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx
Best Answer
Only the size of MDF does not impact IO latency. Latency is related to your disk IO subsystem, its configuration and disk partition alignment.
To find out if disk subsystem is a bottleneck, use
sys.dm_io_virtual_file_statsDMV or Capturing IO latencies for a period of timeHaving multiple data files on separate LUNs - depends on number of CPU Cores will help to alleviate contention to some extent as well.
Look at the NUMA configuration of your server instance, since there is a single I/O thread and a single lazy writer thread for each NUMA node..
You should look into the autogrowth events and if there are frequent autogrowth events fired, you will see a performance hit. Make sure, you have configured autogorowth to a reasonable value - definitely away from Percent increase. Also, configure Instant file initialization, so that data files can leverage its awesomeness.