SQL Server Storage on Azure VMs: The Premium Storage Gotcha

Shannon Lowder

Shannon Lowder

— 3 min read

SQL Server performance on Azure VMs in 2012 could be excellent or it could be terrible, and the single biggest variable wasn't CPU or memory — it was storage. Microsoft introduced Premium Storage (SSD-backed) in late 2012, and understanding the difference between Standard and Premium, plus the gotchas in how Azure exposes storage to VMs, was the difference between a VM that performed acceptably and one that was inexplicably slow.

Standard vs Premium Storage

Standard Storage in 2012 was backed by spinning hard drives. IOPS limits were per-disk and relatively low — typically 500 IOPS per disk for Standard tier. For SQL Server, which is heavily I/O-bound for most workloads, this wasn't enough for anything other than light transactional loads or development environments.

Premium Storage (introduced as a preview in late 2012) was SSD-backed with significantly higher IOPS limits: a P30 disk (1TB) delivered up to 5,000 IOPS sustained, and the IOPS were consistent rather than bursty. For production SQL Server workloads, Premium Storage was required. Standard Storage was for dev/test.

The IOPS Limit Architecture

The gotcha that wasn't obvious: IOPS limits in Azure existed at multiple levels simultaneously.

Per-disk limit: Each attached data disk had an IOPS limit based on its size tier.

Per-VM limit: The VM size itself had a maximum uncached IOPS limit that was the ceiling regardless of how many disks you attached or how fast those disks were. A Standard DS3 VM had a max uncached IOPS of around 12,800 in 2012. Attach ten P30 disks with 50,000 potential IOPS and you'd still be capped at the VM's limit.

The caching behavior: Azure offered read caching on data disks. Enabling read caching for data files (which are frequently read) improved effective throughput significantly. But you should never enable write caching on the SQL Server log disk — write caching introduces the possibility of acknowledged writes that haven't been committed to durable storage, which breaks SQL Server's write-ahead logging guarantees.

For a production SQL Server VM in late 2012:

  • OS disk: Standard Storage, OS and SQL Server binaries only — no database files
  • Data disk(s) for data files: Premium Storage P20 or P30, read caching enabled, RAID 0 striped across multiple disks if you needed IOPS beyond a single disk's limit
  • Log disk: Premium Storage, no caching (critically important), separate from data disks to avoid I/O contention between sequential log writes and random data reads
  • TempDB disk: Use the local SSD temp storage included with DS-series VMs if available (non-persistent, but TempDB can always be recreated), or a separate Premium disk if the VM size doesn't include local SSD

NTFS Allocation Unit Size

Format data disks with a 64KB NTFS allocation unit size instead of the default 4KB. SQL Server's data pages are 8KB, and SQL Server reads in 64KB extents. A 4KB allocation unit means an 8-page extent spans 16 allocation units — more metadata overhead per I/O operation. A 64KB allocation unit aligns with SQL Server's I/O pattern.

REM Format data disk with correct allocation unit size
format D: /FS:NTFS /A:65536 /Q /V:SQLData

This isn't exotic tuning — it's in the official SQL Server on Azure VM guidance. It just wasn't the default, so if you formatted using Windows Disk Management with the GUI defaults, you got 4KB and never thought about it.

Measuring What You Actually Got

After configuring storage, verify you got the IOPS you expected. Run diskspd (Microsoft's I/O load tool) against the data and log disks before any SQL Server workload to establish baseline IOPS. If the numbers don't match the theoretical maximum for your disk tier and VM size, something is misconfigured — either the wrong storage tier, wrong caching setting, or a VM size that's hitting its uncached IOPS ceiling.

Storage misconfiguration was the most common cause of poor SQL Server performance on Azure VMs in 2012. The symptoms — slow queries, high I/O waits in DMVs, poor batch throughput — looked like query problems until you checked the underlying storage performance. Check storage first. As always, I'm here to help.

Read more