0

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?

0

2 Answers 2

3

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.

3
  • Suppsoe I assign 300GB to the sql vm. Should I create the drives on the SQL vm or not? Does it matter if I do have data and log files separate?
    – variable
    Sep 15, 2021 at 16:29
  • 1
    If you want to split data and logs on seperate volumes, do it at the hypervisor level. Better to avoid creating multiple partitions on same disk. If you need to increase the size of one of the partition later, it won't be fun. Sep 15, 2021 at 18:17
  • What about each SAN LUN having dedicated disks and the LUN is then assigned to a drive on sql sever?
    – variable
    Jul 16, 2022 at 17:23
3
  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
  1. 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 .

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