VM size: Performance best practices for SQL Server on Azure VMs

Applies to: SQL Server on Azure VM

This article provides VM size guidance a series of best practices and guidelines to optimize performance for your SQL Server on Azure Virtual Machines (VMs).

There's typically a trade-off between optimizing for costs and optimizing for performance. This performance best practices series is focused on getting the best performance for SQL Server on Azure Virtual Machines. If your workload is less demanding, you might not require every recommended optimization. Consider your performance needs, costs, and workload patterns as you evaluate these recommendations.

For comprehensive details, see the other articles in this series: Checklist, Storage, Security, HADR configuration, Collect baseline.

Checklist

Review the following checklist for a brief overview of the VM size best practices that the rest of the article covers in greater detail:

• The new Ebdsv5-series provides the highest I/O throughput-to-vCore ratio in Azure along with a memory-to-vCore ratio of 8. This series offers the best price-performance for SQL Server workloads on Azure VMs. Consider this series first for most SQL Server workloads.
• Use VM sizes with 4 or more vCPUs like the E4ds_v5 or higher.
• Use memory optimized virtual machine sizes for the best performance of SQL Server workloads.
• The Edsv5 series, the M-, and the Mv2- series offer the optimal memory-to-vCore ratio required for OLTP workloads.
• The M series VMs offer the highest memory-to-vCore ratio in Azure. Consider these VMs for mission critical and data warehouse workloads.
• Use Azure Marketplace images to deploy your SQL Server Virtual Machines as the SQL Server settings and storage options are configured for optimal performance.
• Collect the target workload's performance characteristics and use them to determine the appropriate VM size for your business.
• Use the Data Migration Assistant and SKU recommendation tools to find the right VM size for your existing SQL Server workload.

To compare the VM size checklist with the others, see the comprehensive Performance best practices checklist.

Overview

When you're creating a SQL Server on Azure VM, carefully consider the type of workload necessary. If you're migrating an existing environment, collect a performance baseline to determine your SQL Server on Azure VM requirements. If this is a new VM, then create your new SQL Server VM based on your vendor requirements.

If you're creating a new SQL Server VM with a new application built for the cloud, you can easily size your SQL Server VM as your data and usage requirements evolve. Start the development environments with the lower-tier D-Series, B-Series, or Av2-series and grow your environment over time.

Use the SQL Server VM marketplace images with the storage configuration in the portal. This makes it easier to properly create the storage pools necessary to get the size, IOPS, and throughput necessary for your workloads. It is important to choose SQL Server VMs that support premium storage and premium storage caching. See the storage article to learn more.

Currently, the Ebdsv5-series provides the highest I/O throughput-to-vCore ratio available in Azure. If you don't know the I/O requirements for your SQL Server workload, this series is the one most likely to meet your needs. See the storage article to learn more. SQL Server data warehouse and mission critical environments will often need to scale beyond the 8 memory-to-vCore ratio. For medium environments, you may want to choose a 16 memory-to-vCore ratio, and a 32 memory-to-vCore ratio for larger data warehouse environments.

SQL Server data warehouse environments often benefit from the parallel processing of larger machines. For this reason, the M-series and the Mv2-series are good options for larger data warehouse environments.

Use the vCPU and memory configuration from your source machine as a baseline for migrating a current on-premises SQL Server database to SQL Server on Azure VMs. If you have Software Assurance, take advantage of Azure Hybrid Benefit to bring your licenses to Azure and save on SQL Server licensing costs.

Memory optimized

The memory optimized virtual machine sizes are a primary target for SQL Server VMs and the recommended choice by Azure. The memory optimized virtual machines offer stronger memory-to-CPU ratios and medium-to-large cache options.

Ebdsv5-series

The Ebdsv5-series is a new memory-optimized series of VMs that offer the highest remote storage throughput available in Azure. These VMs have a memory-to-vCore ratio of 8 which, together with the high I/O throughput, makes them ideal for SQL Server workloads. The Ebdsv5-series VMs offer the best price-performance for SQL Server workloads running on Azure virtual machines and we strongly recommend them for most of your production SQL Server workloads.

Edsv5-series

The Edsv5-series is designed for memory-intensive applications and is ideal for SQL Server workloads that don't require as high I/O throughput as the Ebdsv5 series offers. These VMs have a large local storage SSD capacity, up to 672 GiB of RAM, and very high local and remote storage throughput. There's a nearly consistent 8 GiB of memory per vCore across most of these virtual machines, which is ideal for most SQL Server workloads.

The largest virtual machine in this group is the Standard_E104ids_v5 that offers 104 vCores and 672 GiBs of memory. This virtual machine is notable because it's isolated which means it's guaranteed to be the only virtual machine running on the host, and therefore is isolated from other customer workloads. This has a memory-to-vCore ratio that is lower than what is recommended for SQL Server, so it should only be used if isolation is required.

The Edsv5-series virtual machines support premium storage, and premium storage caching.

M and Mv2 series

The M-series offers vCore counts and memory for some of the largest SQL Server workloads.

The Mv2-series has the highest vCore counts and memory and is recommended for mission critical and data warehouse workloads. Mv2-series instances are memory optimized VM sizes providing unparalleled computational performance to support large in-memory databases and workloads with a high memory-to-CPU ratio that is perfect for relational database servers, large caches, and in-memory analytics.

Some of the features of the M and Mv2-series attractive for SQL Server performance include premium storage and premium storage caching support, ultra-disk support, and write acceleration.

General Purpose

The General Purpose virtual machine sizes are designed to provide balanced memory-to-vCore ratios for smaller entry level workloads such as development and test, web servers, and smaller database servers.

Because of the smaller memory-to-vCore ratios with the General Purpose virtual machines, it's important to carefully monitor memory-based performance counters to ensure SQL Server is able to get the buffer cache memory it needs. See memory performance baseline for more information.

Since the starting recommendation for production workloads is a memory-to-vCore ratio of 8, the minimum recommended configuration for a General Purpose VM running SQL Server is 4 vCPU and 32 GiB of memory.

Ddsv5 series

The Ddsv5-series offers a fair combination of vCPU, memory, and temporary disk but with smaller memory-to-vCore support.

The Ddsv5 VMs include lower latency and higher-speed local storage.

These machines are ideal for side-by-side SQL and app deployments that require fast access to temp storage and departmental relational databases. There's a standard memory-to-vCore ratio of 4 across all of the virtual machines in this series.

For this reason, it's recommended to use the D8ds_v5 as the starter virtual machine in this series, which has 8 vCores and 32 GiBs of memory. The largest machine is the D96ds_v5, which has 96 vCores and 256 GiBs of memory.

The Ddsv5-series virtual machines support premium storage and premium storage caching.

B-series

The burstable B-series virtual machine sizes are ideal for workloads that do not need consistent performance such as proof of concept and very small application and development servers.

Most of the burstable B-series virtual machine sizes have a memory-to-vCore ratio of 4. The largest of these machines is the Standard_B20ms with 20 vCores and 80 GiB of memory.

This series is unique as the apps have the ability to burst during business hours with burstable credits varying based on machine size.

When the credits are exhausted, the VM returns to the baseline machine performance.

The benefit of the B-series is the compute savings you could achieve compared to the other VM sizes in other series especially if you need the processing power sparingly throughout the day.

This series supports premium storage, but does not support premium storage caching.

Av2-series

The Av2-series VMs are best suited for entry-level workloads like development and test, low traffic web servers, small to medium app databases, and proof-of-concepts.

Only the Standard_A2m_v2 (2 vCores and 16GiBs of memory), Standard_A4m_v2 (4 vCores and 32GiBs of memory), and the Standard_A8m_v2 (8 vCores and 64GiBs of memory) have a good memory-to-vCore ratio of 8 for these top three virtual machines.

These virtual machines are both good options for smaller development and test SQL Server machines.

The 8 vCore Standard_A8m_v2 may also be a good option for small application and web servers.

Storage optimized

The storage optimized VM sizes are for specific use cases. These virtual machines are specifically designed with optimized disk throughput and IO.

Constrained vCores

High performing SQL Server workloads often need larger amounts of memory, IOPS, and throughput without the higher vCore counts.

Most OLTP workloads are application databases driven by large numbers of smaller transactions. With OLTP workloads, only a small amount of the data is read or modified, but the volumes of transactions driven by user counts are much higher. It is important to have the SQL Server memory available to cache plans, store recently accessed data for performance, and ensure physical reads can be read into memory quickly.

These OLTP environments need higher amounts of memory, fast storage, and the I/O bandwidth necessary to perform optimally.

In order to maintain this level of performance without the higher SQL Server licensing costs, Azure offers VM sizes with constrained vCPU counts.

This helps control licensing costs by reducing the available vCores while maintaining the same memory, storage, and I/O bandwidth of the parent virtual machine.

The vCPU count can be constrained to one-half to one-quarter of the original VM size. Reducing the vCores available to the virtual machine achieves higher memory-to-vCore ratios, but the compute cost will remain the same.

These new VM sizes have a suffix that specifies the number of active vCPUs to make them easier to identify.

For example, the M64-32ms requires licensing only 32 SQL Server vCores with the memory, I/O, and throughput of the M64ms and the M64-16ms requires licensing only 16 vCores. Though while the M64-16ms has a quarter of the SQL Server licensing cost of the M64ms, the compute cost of the virtual machines is the same.

Next steps

To learn more, see the other articles in this best practices series:

• Quick checklist

• Storage

• Security

• HADR settings

• Collect baseline

• For security best practices, see Security considerations for SQL Server on Azure Virtual Machines.

• Review other SQL Server Virtual Machine articles at SQL Server on Azure Virtual Machines Overview. If you have questions about SQL Server virtual machines, see the Frequently Asked Questions.