Migrate to Innovate Summit:
Learn how migrating and modernizing to Azure can boost your business's performance, resilience, and security, enabling you to fully embrace AI.Register now
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
The Mbdsv3-series VMs offer the best performance for SQL Server workloads on Azure VMs. Consider this series first for mission critical OLTP and data warehouse SQL Server workloads.
The Ebdsv5-series provides a high I/O throughput-to-vCore ratio, along with a memory-to-vCore ratio of 8:1. This series offers the best price-performance for SQL Server workloads on Azure VMs. Consider these VMs first for most SQL Server workloads.
The M-series family offers VMs with the highest memory allocation in Azure.
The Mbsv3 and Mbdsv3 series VMs provide a high memory allocation and the highest I/O throughput-to-vCore ratio amongst the M-series family, along with a consistent memory-to-vCore ratio of at least 8:1.
Start development environments with the lower-tier D-Series, B-Series, or Av2-series and grow your environment over time.
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. 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 the Azure Hybrid Benefit to bring your licenses to Azure and save on SQL Server licensing costs.
If this is a new VM, then create your new SQL Server VM based on your application requirements. If you're creating a new SQL Server VM for a new application built for the cloud, you can easily scale your SQL Server VM as your data and usage requirements evolve. Start development environments with the lower-tier D-Series, B-Series, or Av2-series and grow your environment over time.
Consider the following VM series based on your SQL Server workloads:
Highest memory allocation for mission critical workloads: The Mbsv3 and Mbdsv3-series VMs offers the highest memory allocation in Azure, with the best storage performance.
High I/O throughput-to-vCore ratio: The Mbsv3 and Mbdsv3-series VMs offer the highest throughput-to-vCore ratio of any VM series in any cloud. The Ebdsv5-series VMs offer the second highest throughput-to-vCore ratio in Azure. If you don't know the I/O requirements for your SQL Server workload, the Ebdsv5-series VMs is the one most likely to meet your needs. See the storage article to learn more.
Parallel processing for larger machines: The Msv3 and Mdsv3 series VMs offer parallel processing, making them good options for larger data warehouse environments.
SQL Server data warehouse and mission critical environments will often need to scale beyond the 8:1 memory-to-vCore ratio. For medium environments, you may want to choose a 16:1 memory-to-vCore ratio, and a 32:1 memory-to-vCore ratio for larger data warehouse environments.
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's important to choose SQL Server VMs that support premium storage performance. See the storage article to learn more.
Memory optimized M-series VMs
The M-series offers vCore counts and memory for some of the largest SQL Server workloads.
The following lists the capabilities of the M-series VMs:
Are suitable for SQL Server workloads that require high computing capabilities with large memory footprints and less emphasis on storage performance.
Mbsv3 and Mbdsv3 series
The Mbsv3 and Mbdsv3 series are memory optimized VMs designed for large in-memory databases and workloads with high memory-to-CPU ratio needs. The VMs in this series are built on the 4th generation Intel® Xeon® Scalable and offer a range of memory sizes and vCPU counts to meet the needs of your SQL Server workloads. The Mbsv3 and Mbdsv3 series VMs are recommended for mission critical and data warehouse workloads.
The Mbsv3 and Mbdsv3 series VMs support support large in-memory databases and workloads with a high memory-to-CPU ratio perfect for relational database servers, data warehousing, heavy reporting, large caches, and in-memory analytics.
The following are characteristics of VMs in this series:
Mbsv3-series offer up to 176 vCores, and 1,536 GiB of memory, with a consistent 8:1 memory-to-vCore ratio, 650,000 IOPS, and 6,000 MBps of storage throughput.
Mbdsv3-series offer up to 176 vCores and 4 TiB of memory, 650,000 IOPS, and 10,000 MBps of storage throughput. This VM series offers over a 50% improvement in IOPS and throughput to the top-performing Ebdsv5 series - making the Mbdsv3 one of the highest-performing VM options available in any cloud. The Mbdsv3 VM series shares similar performance characteristics with the Mbsv3 VM series but includes strong local and ephemeral storage, making it perfect for tempdb performance optimization, reporting, mission critical OLAP, and data warehousing workloads.
Msv3 and Mdsv3 series
The Msv3 and Mdsv3 virtual machines are designed with computing power and memory capabilities at medium, high and very high memory levels. These VMs provide improved performance, scalability, and resilience to failures compared to the previous generation Mv2 VMs.
The following lists the VMs in this series:
Msv3 and Mdsv3 Medium Memory VMs: powered by 4th generation Intel® Xeon® Scalable processors, and offers VM sizes of up to 4 TiB of memory, 416 vCPUs, 130,000 IOPS, and 4,000 MBps of remote storage throughput with the NVMe interface.
Msv3 and Mdsv3 High Memory VMs: are designed for high memory workloads with memory ranging from 6 TiB to 16 TiB, up to 832 vCPUs, up to 260,000 IOPS and 8,000-MBps throughput to remote storage with the NVMe interface.
Mdsv3 Very High Memory Series: powered by 4th generation Intel® Xeon® Platinum 8490H (Sapphire Rapids) processors, and have the largest memory footprint of any of the M-series based virtual machines offering up to 32 TiB of memory, 1,792 vCPUs, up to 200,000 IOPS and 8,000 MBps of remote storage throughput.
Caution
Disable SMT to use SQL Server on Azure VMs that exceed 64 vCores per NUMA node.
Memory-optimized E-series VMs
The E-series VMs are designed for memory-intensive workloads, such as large databases, big data analytics, and enterprise applications that require significant amounts of RAM to maintain high performance.
Ebdsv5-series
The Ebdsv5-series is a 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:1 which, together with the high I/O throughput, makes them ideal for most 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.
Note
The larger Ebdsv5-series sizes (48 vCPUs and larger) offer support for NVMe enabled storage access. In order to take advantage of this high I/O performance, you must deploy your virtual machine using NVMe.
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 ECadsv5-series virtual machine sizes are memory-optimized Azure confidential VMs with a temporary disk. Review confidential VMs for information about the security benefits of Azure confidential VMs.
As the security features of Azure confidential VMs might introduce performance overheads, test your workload and select a VM size that meets your performance requirements.
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 does not have the memory-to-vCore ratio of 8 that is recommended for SQL Server workloads. As such, consider using these virtual machines for small applications and development workloads only.
DCadsv5-series
The DCadsv5-series virtual machine sizes are general purpose Azure confidential VMs with temporary disk. Review confidential VMs for information about the security benefits of Azure confidential VMs.
As the security features of Azure confidential VMs can introduce performance overheads, test your workload and select a VM size that meets your performance requirements.
B-series
The burstable B-series virtual machine sizes are ideal for workloads that don't 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 burstable B-series 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.
The burstable B-series does not have the memory-to-vCore ratio of 8 that is recommended for SQL Server workloads. As such, consider using these virtual machines for smaller applications, web servers, and development workloads only.
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 can also be a good option for small application and web servers.
Note
The Av2 series does not support premium storage and as such, is not recommended for production SQL Server workloads even with the virtual machines that have a memory-to-vCore ratio of 8.
Storage optimized
The storage optimized VM sizes are for specific use cases. These virtual machines are specifically designed with optimized disk throughput and IO.
Lsv2-series
The Lsv2-series features high throughput, low latency, and local NVMe storage. The Lsv2-series VMs are optimized to use the local disk on the node attached directly to the VM rather than using durable data disks.
These virtual machines are strong options for big data, data warehouse, reporting, and ETL workloads. The high throughput and IOPS of the local NVMe storage is a good use case for processing files that will be loaded into your database and other scenarios where the data can be recreated from the source system or other repositories such as Azure Blob storage or Azure Data Lake. Lsv2-series VMs can also burst their disk performance for up to 30 minutes at a time.
These virtual machines size from 8 to 80 vCPU with 8 GiB of memory per vCPU and for every 8 vCPUs there's 1.92 TiB of NVMe SSD. This means for the largest VM of this series, the L80s_v2, there's 80 vCPU and 640 BiB of memory with 10x1.92 TiB of NVMe storage. There's a consistent memory-to-vCore ratio of 8 across all of these virtual machines.
The NVMe storage is ephemeral meaning that data will be lost on these disks if you deallocate your virtual machine, or if it's moved to a different host for service healing.
The Lsv2 and Ls series support premium storage, but not premium storage caching. The creation of a local cache to increase IOPs isn't supported.
Warning
Storing your data files on the ephemeral NVMe storage could result in data loss when the VM is deallocated.
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's 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.
Note
Medium to large data warehouse workloads may still benefit from constrained vCore VMs, but data warehouse workloads are commonly characterized by fewer users and processes addressing larger amounts of data through query plans that run in parallel.
The compute cost, which includes operating system licensing, will remain the same as the parent virtual machine.
Next steps
To learn more, see the other articles in this best practices series:
Azure HPC is a purpose-built cloud capability for HPC & AI workload, using leading-edge processors and HPC-class InfiniBand interconnect, to deliver the best application performance, scalability, and value. Azure HPC enables users to unlock innovation, productivity, and business agility, through a highly available range of HPC & AI technologies that can be dynamically allocated as your business and technical needs change. This learning path is a series of modules that help you get started on Azure HPC - you
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Learn how you can use the Azure portal to deploy your SQL Server on Azure VM with Premium SSD disks (preview), the new generation storage solution designed for IO-intensive enterprise workloads that require submillisecond disk latencies, high IOPS and throughput at a low cost.
When migrating your SQL Server workloads to Azure VM, there are literally hundreds of different VM sizes to choose from. Hopefully, you've seen our videos on sizing so you have an idea of the methodology we recommend to choose the right size, but what if you have a new SQL Server workload that you don't have metrics for? Where should you start? In part 4 of this eight-part series, we will highlight the Edsv4-series VMs and show you why we think they are the best VMs for your SQL Server workloads.[00:44] Wha
There are a number of different VM sizes available to host your SQL Server workloads, but it can sometimes be a challenge to find the right mix of vCPUs and I/O throughput for your needs. We’re thrilled to introduce a new series of VMs built specifically for I/O intense workloads like SQL Server – the Ebdsv5 series, now in public preview. In this episode with Anna Hoffman, Priya Shan, and Pam Lahoud, we’ll cover what we love about this new series, and why it offers the best price-performance for your SQL Se
Learn how to migrate your SQL Server on Azure Virtual Machine (VM) log disk to an Azure Ultradisk to take advantage of high performance and low latency.
