1

Per the Performance best practices for SQL Server in Azure Virtual Machines documentation:

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance

There is a note with regards do disk striping:

Use data disks for data and log files: If you are not using disk striping, use 2 Premium Storage P30 disks where one disk contains the log file(s) and the other contains the data and TempDB file(s). Each Premium Storage disk provides a number of IOPs and bandwidth (MB/s) depending on its size, as described in the following article: Using Premium Storage for Disks. If you are using a disk striping technique, such as Storage Spaces, you must place all files on the same drive.

Is there a specific reason why this would be the case?

1

Is there a specific reason why this would be the case?

If you are using two disks where one disk contains the logs files and the other contains the data and TempDB files, so the paths are different.

Let's say, disk1E:\ for log files, disk2F:\ for data files, so your data default location is E:\, your Log default location is F:\.

If you don't want to use two disks for SQL, you can use a disk striping technique, such as storage spaces, in this way, you should change log files path and data files path to the same drive.

For example, you create a storage spaces, the path like E:\, your Data default location should be E:\dataand log default location should be E:\log, like this:

enter image description here

Hope this helps.

  • Hi Jason, I am still not clear on this particular guidance: "If you are using a disk striping technique, such as Storage Spaces, you must place all files on the same drive." Why is it not possible to use strip and use different volumes? – Charles Chen Feb 21 '18 at 8:11
  • @CharlesChen It means you can use different volumes in the same drive(or storage pool). – Jason Ye Feb 21 '18 at 8:14
  • @CharlesChen Does that work for you? please let me know if you need more help:) – Jason Ye Feb 21 '18 at 8:48
  • I think the MS guidance is poorly worded. For example, if you are using P10 drives (500 IOPS ea) and want 2000 IOPS for SQL server, you can either go a striping route (e:\ is a storage space made up of 4 drives), or create e:\, f:\, g:\ h:\, with you manually spreading your SQL data files across those drives. Of course, you can mix and match as well (e:\ striped with 3 drives for your user db, f:\ by itself for tempdb, etc). – StrayCatDBA Feb 22 '18 at 22:58
  • There is no "right" answer as to whether it's best to let windows spread your data around (1 volume made from several disks) or let SQL spread your data around (multiple database files spread across multiple volumes.) – StrayCatDBA Feb 22 '18 at 23:05

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.