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?

  • 1
    Separating the io will help if you can get dedicated storage on the SAN to create the additional IO paths. You will need to work with your SAN admin and VM admin to get that setup and configured. The SAN manufacturer can also help in defining the requirements for SQL Server and how best to configure the disks. On direct attached storage each database log should have it's own dedicated array of drives as the IO pattern is a sequential write and the throughput gets affected when it shares the same disks as the data files are accessed in a random IO pattern. SAN recommendations could be different – Aaron Aug 27 '15 at 16:21
  • 2
    Autogrowth on data and log files is set properly ? For data files, make sure you have Instant file initialization enabled. Check my answer for more details. Also, how is tempdb configured ? Have you checked "The data loading performance guide". – Kin Shah Aug 27 '15 at 16:40
  • 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
  • Not necessarily an answer but is it possible that your storage array is doing some sort of caching. I have seen in some scenarios where there disk cache flush can actually cause SQL server to show waits where they are not actually occurring simply because the disk or RAID cache is flushing. – Shooter McGavin May 3 '18 at 17:16

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

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.