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?