Applies toSQL Server on Azure Virtual Machines (Windows and Linux) with Premium SSD, Premium SSD v2 and Ultra Disk; SQL IaaS Agent extension
Start from a baseline
Capture IOPS, throughput (MB/s) and latency per volume from the current server at peak (see the sizing for the cloud post). Separate data reads, data writes and log writes.
Layout
| Volume | Recommendation |
|---|---|
| Data files | Premium SSD v2 (size, IOPS and throughput set independently) or Premium SSD with read-only host caching |
| Log files | Premium SSD v2 or Premium SSD with no host caching; Ultra Disk for very low latency needs |
| tempdb | Local temporary SSD (D: on Windows) where the VM size has enough; recreated at boot |
| OS | Standard Premium SSD, default caching |
| Backups | Back up to URL (Blob) rather than local disks |
Premium SSD v2 and Ultra Disk don't support host caching; size IOPS/throughput directly.
Striping
- Pool several disks with Storage Spaces (Windows) or LVM (Linux) to add their IOPS/throughput.
- Use 64 KB interleave for data, and format NTFS with 64 KB allocation unit size.
- The SQL IaaS Agent extension can configure storage pools when deploying from marketplace images.
VM-level limits
Each VM size has caps on:
| Limit | Applies to |
|---|---|
| Uncached disk IOPS and MB/s | Disks without host cache (logs, Premium SSD v2) |
| Cached + temp storage IOPS and MB/s | Disks with read-only caching and local SSD |
| Network bandwidth | Backups to URL, AG traffic |
If disks provide more than the VM's cap, you pay for performance you can't use. Choose memory-optimized sizes with high storage throughput (e.g. Ebdsv5-type families) for heavy I/O. Check current sizes and limits.
tempdb on local SSD
- Configure the SQL IaaS extension or a startup script to create the folder before SQL Server starts.
- Multiple equal-size data files (up to 8 to start).
- Ensure the VM size's temp disk is large enough for tempdb peaks (see the tempdb post).
Validate
- Run DiskSpd (Windows) or fio (Linux) against each volume before go-live.
- Compare with baseline and with VM limits.
- Monitor disk latency and VM "IOPS consumed %" metrics in Azure Monitor after go-live.
Tip
When disk latency rises but disk metrics show headroom, check the VM-level "Data Disk IOPS Consumed Percentage" and "Bandwidth Consumed Percentage". VM caps are a common hidden bottleneck.
Checklist
- Baseline IOPS/MB/s/latency captured
- Data, log, tempdb separated with correct caching
- Striping and 64 KB allocation units
- VM size limits match disk performance
- tempdb on local SSD sized and automated
- DiskSpd/fio validation done