Επεξεργασία

Κοινή χρήση μέσω

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:

  • Identify workload performance characteristics to determine the appropriate VM size for your business.
  • If you're migrating to Azure, use tools like Data Migration Assistant and SKU recommendation to find the right VM size for your existing SQL Server workload, and then migrate with Azure Data Studio.
  • Use Azure Marketplace images to deploy your SQL Server VMs as the SQL Server settings and storage options are configured for optimal performance.
  • Use VM sizes with 4 or more vCPUs.
  • Use memory optimized virtual machine sizes for the best performance of SQL Server workloads.
    • The Edsv5-series, and the Msv3 and Mdsv3-series offer an optimal memory-to-vCore ratio recommended for OLTP workloads.
    • 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.

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. 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:

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 Edsv5-series virtual machines support premium storage, and premium storage caching.

ECadsv5-series

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 virtual machines support premium storage and premium storage caching.

Note

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.

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

Note

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: