Azure Disk performance optimizaton in SQL VM

Question

We are using Azure SQL VM and we have multiple data files of multiple databases stored on same drive (let's say Drive E: of size 3 TB) which is created from 4 * 512 GB disks + 4 * 256 GB disks.

I was thinking to remove those smaller disks and add a bigger disk like 1 TB or 2 TB.

What would you recommend based on the following for getting best performance from disks:

1. single disk of 3TB.
2. 3 Disks of 1 TB each.
3. 1 Disk of 1 TB and 1 Disk of 2 TB.

It's Azure premium SSD.

---

I checked the documentation and on my understanding using P30 and P40 disks i.e. 1TB and 2TB disks respectively seems to be better. However, if my single database size is around 1.7 TB in size, I'm confused if that would be better to use single 2TB for it or combine two 1TB disks.

• Francesco Mantovani: Well, this came out today: brentozar.com/archive/2023/09/…

Answer 1

Luckily there's a doc on this: Storage: Performance best practices for SQL Server on Azure VMs

For production workloads, use the P30 and/or P40 disks for SQL Server data files to ensure caching support and use the P30 up to P80 for SQL Server transaction log files. For the best total cost of ownership, start with P30s (5000 IOPS/200 MBPS) for data and log files and only choose higher capacities when you need to control the VM disk count. For dev/test or small systems you can choose to use sizes smaller than P30 as these do support caching, but they don't offer reserved pricing.

so "3 Disks of 1 TB each."

Answer 2

However, If my single database size is around 1.7 TB in size, I'm confused if that would be better to use single 2TB for it or combine two 1TB disks

its Premium SSD provided by Azure

Nowadays on non-mechanical (HDD) disks, and with virtualization, it generally doesn't matter much how you split up your data. The exception to that being in the cloud, where generally the IOPs are more limited.

Like Aleksey mentioned, you should figure out what the max IOPs are for a single 2 TB disk vs two 1 TB disks combined in Azure, and choose the setup with the higher total IOPs.

Prior to virtualization, where each disk was a physical separate disk, the general recommendation was to provision a drive for all of the Data files, a separate drive for all of the Log files, and a separate drive for TempDB. And in rare cases, an extra drive or two, for the Data and Log files of any super busy databases. But being in the cloud, your main limitation is likely to be IOPs.