Is there any benefit that justifies separating the os, mdf, ldf and tempdb onto separate volumes?

RAID and redundancy is already included.

Connection options to the host are:

  • 16 x 16/8/4 Gb Fibre Channel
  • 8 x 10 Gb Fibre Channel over Ethernet (FCoE); and
  • 8 x 10 Gb iSCSI

...so I don't think there is a concern with that being a bottleneck.

The FlashSystem datasheet is here:

IBM FlashSystem V9000


One reason for separate drives even with SAN is that a given disk or set of disks can only do so much I/O at once. Any of the things on the drive could saturate the I/O on the SAN by keeping OS, TempDB, logs, page file, and the user database on the same drive. Depending on the query and server configuration, TempDB, the page file, and the LDF could all be in contention for the I/O. By separating those, the I/O for those set of disks is exclusive to where they are allocated.

Another thing to consider is any one of those things could also use all the available space if sharing disk space and instead of one component shutting down, the whole server could be shut down.

  • I realize this is true for standard hard drives but IBM FlashSystem is all flash drives. IOPS (4K) using Real-time Compression 1,200,000 – stacylaray Mar 19 '16 at 4:26
  • Also, different things could benefit from different allocation unit sizes, like the MDFs using 64k and LDFs being 8k. Main difference in sizes are space wasted and SQL performance. – Antoine Hernandez Mar 19 '16 at 4:37
  • Per Brent Ozar - When the SQL Server engine starts to write data, it essentially: 1. Writes to the log file that it’s going to change the data 2.Writes to the data file 3. Writes to the log file to mark the transaction finished. On a HDD, the disks will jump from the sequential log file location to the random data file location and back to the sequential log file location which is inefficient. This does not apply to flash storage. Also, at over a million IOPS, I'd be hard pressed to saturate. If any one of the components goes down, I'm down, so that's a moot point. – stacylaray Mar 19 '16 at 5:12
  • @stacylaray That's not entirely accurate. The main reasons for creating separate logical volumes is for multipathing and better use of the HBAs. I'd highly doubt with a single logical volume and out of the box HBA setup you'd be able to drive the IO to millions of IOPs. Incorrect HBA configuration, old storport.sys usage, and incorrect multipathing will literally stop your IO from ever reaching that. – Sean Gallardy Mar 20 '16 at 20:59
  • @stacylaray The way that flash internally writes has it's own wear leveling and TRIM implementations. Thus, saying that 1 IO from a server uses 1 IO on the SAN is not even close to accurate. Given write amplification it's probably more like 1.4 or 1.5 to 1 and in worse case scenarios more around 4-5 to 1. – Sean Gallardy Mar 20 '16 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.