Different Disk Drive Same Backend Storage

Question

I have a database running on a VM which is getting hammered during a large load, in particular I can see waits of WRITELOG happening. My initial thought is to split the files out on to their own drives but the backend storage is the same as where the other DB files are sitting.

Basically it is SAN presented as a Cluster Shared Volume to a whole host of virtual machines.

Would there be a performance advantage in doing this? Some memory in the depths of my brain is telling me something about the number of IO streams would be better potentially?

---

To update this I have now separated out the files and correctly sized the transaction log. I have been collecting information from sys.dm_io_virtual_file_stats and can see that I now have extremely high readIOStalls but with a low latency of 13ms. I also collected some memory information and PLE was in the thousands on average with this being a 32GB system I would expect that apart from in one 30min period where it drops right down to 30 before climbing sharply again, at this time lazy write/sec increases to 50 also before reducing to 0. Could this period be the cause of the large number of read stalls I am seeing? I would have expected to see with such high Read Stalls also high latency?

Answer 1

This will depend on your exact SAN. But in a former job we had a SAN with auto-tiering. In this case, in theory you can divide up your database into files and the most active files would automatically be moved to flash storage by the SAN

Answer 2

Writelog wait stats represent issues related to log file or there is some sort of issue writing data to log file/log file access is slow.

Check the log file. If it is on the same drive try to move it to a different drive. Also put the auto-growth amount much higher than the current parameter.

This is what I can understand based on the above details provided.

Please let us know if this isn't what you are expecting.

Answer 3

What version of sql are you running? As far as checking log size, are you meaning virtual log files? What size of DB are we talking here? I'm sure there's all kinds of great guidance on growing out the log properly, but be aware of post sql 2014's changes in VLF creation where at a certain threshold instead of breaking vlfs up to 16, it switches to 1 so if you're at 8gb to create 512mb vlfs when you hit that threshold it'll then create an 8GB vlf you'll have to then shrink the log all over again. Also if your DB's integrity isn't paramount, you could use delayed durability = forced, which reduces writelog waits at the expense of potentially losing more data in a DR scenario.