Why is it not important whether data and log files share the same disk or not in a SAN? [closed]

Question

I have a hard disk. I create 3 drives on it. One for OS. One for sql data files. One for sql log files.

However I am reading that suppose my storage layer is not a hard disk, but a SAN based infrastructure, then it is irrelevant where the data and log files are placed because all access is random.

What does this mean?

Answer 1

When on a SAN, you do not write to 1 specific disk but to a set of disks (managed at the SAN level).

When you use physical disk, you want to split IO to prevent from IO bottleneck causing performance issue (P.s. Splitting data and log is an old habbit and may not be the best way to split your IO... But you probably want to make sure tempdb is on a different disk). When you write to a SAN, your IO are already split on different disk by the SAN so it doesn't need to be different virtual disk.

You could do it to simplify troubleshooting (identify an issue with your log for exemple). If it's on a different virtual drive, you'll be able to get segregated "stats" from perf mon for example.

Other then that, I can't think of any good reason to create multiple disk when using a SAN infrastructure.

Answer 2

1. The focus seems to be on performance. However there are other things you would like to be concerned about. For example:

• disk failure: if on the same disk, then you lose both data and logs, otherwise if the the logs are safe you may be able to restore the db
• disk full: if on the same disk, if logs run too big and fill up the disk, then the write to the data will fail, and vice versa. By keeping the filesystems separate you minimize the impact

2. Even regarding performance, the situation may be the same on the disk storage side, but not on the OS side:

• The OS still sees two separate disks (unaware that they reside on the same SAN), and treats them accordingly assigning separate resources to each disk. So should be faster.
• You can configure disk attributes separately for better performance. Data is random I/O with more Reads than Writes, but logs are sequential and Write operations. You can finetune the disks, buffers, block size etc. to gain max performance for these different workloads. If everything on one disk you dont have good finetune options .