SQL Server : multiple databases with transaction log on one SSD array

Question

I know for HDD arrays that having multiple log files from different databases has a performance hit as it changes the IO pattern from sequential to random for the array. So having an array per database for it's logs is a best practice.

Does anyone have any suggestions about running multiple SQL Server databases with the log files for each of those databases existing on one SSD array?

Does anyone have experience with the differences of Hitachi's FMDs vs SSDs for their HUS hardware? I'm concerned with consolidating individual drives down to a few FMDs and not being able to sustain performance between the user databases and tempdb if all I have is disk pool of 4 FMDs for everything.

Answer 1

Changing from sequential to logical read / write patterns is far more harmful on rotational drives than SSDs. Because SSDs have no read heads, latency is much lower, even on non-sequential reads and writes. You will take a performance hit if it's not sequential, but with higher-end SSDs, you'll be able to get close to or saturate bus speeds (SAS / SATA) on the system. You'll need to make sure that whatever you're driving your array with can sustain the I/O. I've found CrystalDiskMark benchmarks w/ 4k @ 32QD have been accurate indicators of performance on a busy system. All that said, if your SSD array can sustain the same IOPS across all logs with random I/O as what your rotational drives can do sequential, you'll be fine.

I don't have any experience with FMDs vs SSDs.