5

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?

  • Log files not set greatly so will shrink and resize correctly and see how it performs. Thanks. – Tom Aug 28 '15 at 13:46
  • Storage where your data and log files are correctly formated ? 64K Cluster Size ? Disks of you SAN are SSD ? – PVC Apr 30 '18 at 9:42
0

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

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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