The question:
with reference to: Azure Managed Disks
SQL Server 2019; Windows 2016
C: OS maps to 128 GB (S10 @ 60mb/sec).
So far so good.
I have drives for Data, Index, TempDB, Log and Local Backups.
Data, Index, TempDB disk pattern: Read/Write; Log and Local Backups: (mostly) sequential writes.
Now here it gets iffy:
Do I prefer to have multiple Azure disks (eg 1 x P10 @ 100mb/s [128 GB], 3 x P20 @150mb/s [512 GB])
OR do I go bigger (with higher throughput) and store all on the same disk (eg P40 @250mb/s [2 TB])?
This to allow OS to be presented with a Data drive of 512GB, and Index drive of 512GB, and TempDB drive of 128GB. SQL Log and Backup of 512 GB.
This question speaks to SQL Server accessing database files through the operating system, and virtual infrastructure exposing virtual disks…
Is there a 250mb/s capacity for each thread targeting the P40 disk?
If I have this 2TB VHD file allocated as 4 logical disks in the OS of the VM… would it be more performant than 3 lower tier disks allocated as individual drives?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
The 250 mb/s is the capacity as a whole, not per access thread.
A few other points. There should be a tier of Azure-VM that offers an SSD as a temp drive that you should definitely use for your TempDB. It may take a bit to make sure the permissions are setup so that when the machine reboots it has proper permissions to the TempDB there, but it will be worth it. That is a FAST disk, and while it is transitory (not persisted across reboots) it’s where I’ve put my TempDB in the past when I used SQL Server on Azure-VM.
Finally, I would have separate disks (or groups of disks) per drive that you need. If you stripe the disks you get vastly increased throughput as an aggregate… up to about 4 disks, after which you don’t get much more actual throughput.
So, my initial setup is usually:
Azure VM
- C: – OS and SQL engine installation.
- Use the included SSD for my TempDB
- 1 Disk (or set of disks) for Data
- 1 Disk (or set of disks) for Log
If I have multiple IOPS hungry databases then I will usually include additional drives for them to isolate their required throughput.
Final thought: it may be tempting to use SSD for all your drives, but take a look at if standard disks can provide enough throughput (especially after striping them). With standard disks you only pay for what you actually store, not what you reserve; while SSD’s you pay for what you reserve regardless of how much you use.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0