·
6 min read

Azure Premium Storage provides highest performance for SQL Server in Azure VM

Last week we announced the General Availability of Azure Premium Storage for Azure Virtual Machines.

Premium Storage provides steady high throughput (up to 64,000 IOPs; 8x more than Standard Storage) at low latency (single-digit milliseconds; 8x less than Standard Storage). This enables enterprise SQL Server workloads (OLTP or Data warehousing) needing consistent high performance to run on Azure VM.

During the Preview of Azure Storage we worked with many SQL Server customers of different workload sizes to ensure that Premium Storage satisfied their requirements on Azure VM.  Here are some examples of customer results:

  • Transaction latency for thousands of concurrent users consistently within 10ms
  • Query times over large data sets reduced from minutes in standard storage to seconds
  • Batch loads for millions of records reduced from hours in standard storage to minutes
  • Backup/restore times on large databases reduced from many hours in standard storage to less than one hour

Azure Premium Storage and SQL Server

Premium Storage is based on Solid State Disks (SSD) in the storage backend, dedicated fast connections between the storage backend and new compute clusters, and VMs local read-only caches that are also SSD-based. Writes are sent to the backend to guarantee their persistence via 3 copies. Writes trigger an update on the VM read-only cache. Reads that can be served from the cache return immediately; others are served quickly from the backend, also updating the cache as a result. More details here.

VMs using Premium Storage get a guaranteed higher storage bandwidth to serve writes and reads. Reads served from the cache are not counted towards this bandwidth. The high bandwidth allows writing and reading more data per second to the storage. This increases transaction throughput and reduces the time for query scans and other operations such as backup/restore, batch loads, and index rebuilds.

The following PerfMon picture shows a SQL Server backup consistently reading and writing ~500MB/s:

The main benefit of the fast storage writes is lowering SQL Server transaction latency. This is achieved by shortening the time to synchronously write commit records to the log file. This benefits both standalone and AlwaysOn configurations, where the secondary must ACK writing commit records. Besides this, the fast storage writes reduce the time for other SQL Server write operations (e.g. checkpoints (asynchronously writing dirty pages to disk), AlwaysOn secondary’s log redo, etc).

The main benefit of fast reads is lowering SQL Server query time. This is achieved by shortening the time to retrieve data pages, especially if served from the read-only cache. In addition, the higher storage bandwidth aids retrieving more data pages. The benefit of the read-only cache is for data files, as data pages are read very frequently. There is no benefit for log files, as log records are only read during distant operations (e.g. backups).

The following PerfMon picture shows a SQL Server workload executing an average of 9K Batch Requests per second. This accounts for 20K reads and 17K writes per second (37K IOPs). The average read latency is just 1ms with a max of 6ms, and the average write latency is just 3ms with a max of 10ms.

Azure Premium Storage Options

There are 3 types of Premium Storage disks to choose from: P10, P20, and P30. The type of disk is determined by its size. Each disk type is assigned a different number of IOPs and bandwidth:

Disk Type

Disk Size

Storage IOPS

Storage Bandwidth (MB/s)

P10

128 GB

500

100

P20

512 GB

2300

150

P30

1024 GB

5000

200

 

To support Premium Storage, there is a new series of VMs called DS-Series. The capabilities of these VMs are below:

 VM Size

CPU Cores

Max

Storage Disks

Max Storage Space

Max Storage IOPS*

Max Storage Bandwidth (MB/s)

Cache size (GB)

DS1

1

2

2 TB

3,200

32

43

DS2

2

4

4 TB

6,400

64

86

DS3

4

8

8 TB

12,800

128

172

DS4

8

16

16 TB

25,600

256

344

DS11

2

4

4 TB

6,400

64

72

DS12

4

8

8 TB

12,800

128

144

DS13

8

16

16 TB

25,600

256

288

DS14

16

32

32 TB

50,000

512

576

  * Doesn’t include IOPs directly from the VM read-only cache

Notice that the total number of IOPs and bandwidth will depend on the combination of VM size, number of disks, and the sizes of these disks.

Consider the size of your database, workload requirements, and pricing when choosing the above. Notice that a VM can have disks with different sizes and, it’s even possible to mix disks from Premium and Standard storage. More details here.

Creating a new SQL Server Virtual Machine using Premium Storage

  1. Go to the new Azure Portal
  2. Create a storage account of type Premium Locally Redundant

    Notice that there is a limit of 32TB per storage account. If you need more storage, then create more storage accounts.
  3. Create a new VM using a SQL Server Image from the Gallery, specifying a DS-Series VM, and the Premium Storage account that you previously created (type PREMIUM-LRS). Notice that this VM can’t be added to resource groups that have other VM Series (DS-Series are hosted by new compute clusters).

  4. Attach disks to the VM
    Select the VM that you previously created, go to Disks, and select Attach New. Choose the Premium Storage account that you previously created, a container for the disk (by default vhds), the disk file name, size, and caching. A common basic configuration is using 3 disks, one for data, another for log, and another for TempDB.

Migrating an existing SQL Server to Premium Storage

Notice that it’s not possible to upgrade an existing Standard Storage account to Premium Storage and that DS-Series VMs can’t be added to a Resource Group that have other VM Series. 

To migrate an existing SQL Server to Premium Storage please create a new DS-Series VM that uses a Premium Storage account. Then backup and restore your databases and copy your SQL configuration (equivalent to a side-by-side migration).

To reduce downtime during the migration to few minutes:

  1. Take a full backup of the databases and restore them to the new SQL VM
  2. Disconnect the clients from the databases in the old SQL VM
    ALTER DATABASE SET SINGLE_USER WITH ROLLBACK AFTER 20 SECONDS
  3. Take a log backup of the databases (for any final transactions) and restore them to the new SQL VM
  4. Change the clients connection string to point to the new SQL VM

If you are using SQL AlwaysOn Availability Groups you can minimize downtime during the migration to seconds. Availability Groups allow you to failover a group of databases from a primary SQL Server replica to a secondary SQL Server replica in seconds without data loss. In addition, applications connect to the primary replica using a listener (virtual network name), so their connection string doesn’t need to change.

You can add a synchronous secondary SQL Server replica in a DS-Series VM that uses Premium Storage and failover to it. Notice that you will need to add the secondary replica VM to the same Windows Domain and the same Windows Cluster as the primary replica. In addition, you will need to create an endpoint for the secondary replica VM and add it to the load balancer supporting the Availability Group listener.

More details here.

Performance Best Practices

Most existing performance best practices apply to Premium Storage. Premium Storage disks have much higher bandwidth and IOPs limits than Standard Storage disks, thus a smaller number of Premium Storage disks will satisfy the performance requirements (especially for P30 disks). Consider the bandwidth and IOPs limits of the DS-Series VM sizes when determining the number and types of disks.

To get the highest performance:

  1. Use a Premium storage account and VM in the same region
  2. Use separate disks for data files, log files, and TempDB files
  3. Enable the read-only cache for data disks and TempDB disks, but not for log disks
  4. If you need higher bandwidth or IOPs: use Storage Spaces over multiple disks to aggregate their IOPs, bandwidth, and storage space. Use separate Storage Spaces for data and log. Depending on your TempDB requirements you could put TempDB in the Storage Pool for data files or in a different pool.

Summary

Premium Storage provides steady high throughput at low latency. This enables enterprise SQL Server workloads (OLTP or Data warehousing) needing consistent high performance to run on Azure VM.

Many SQL Server customers of different workload sizes have satisfied their requirements on Azure VM using Premium Storage. We hope that you will too!

Premium Storage is available in the following regions: West US, East US 2, West Europe, East China, Southeast Asia, West Japan. It’ll become available in other regions soon.

 

Learn more about SQL Server on Azure VM and try Premium Storage today!