2

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

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
5
  • 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, 2016 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, 2016 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, 2016 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, 2016 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, 2016 at 21:01
0

I love that SAN. I used it back in 2015 or 2016 and we got microsecond response from it. I found it useful to separate the volumes anyways for monitoring and performance tuning/isolation. Being able to pull up perfmon metrics per disk (if you have each db on its own drive letter, or tempdb/logs split, etc.) is really helpful at times when working with many different monitoring solutions with different limitations.

It also helps you maintain a standard moving fwd on non optimized disk environments.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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