Uredi

Deli z drugimi prek

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

  • Članek

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 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 and the Ebdsv5 series, the Msv3 and Mdsv3 series offer an 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 OLTP 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.
  • Use Azure Data Studio to migrate to Azure.

Warning

Installing SQL Server to systems that exceed 64 cores per NUMA node is not currently supported. This limitation currently applies to the Standard_M176s_3_v3 and Standard_M176s_4_v3 Azure Virtual Machine sizes within the Msv3 and Mdsv3 Medium Memory Series.

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's 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.

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. NVMe support for SQL Server marketplace images will be coming soon, but for now you must self-install SQL Server in order to take advantage of NVMe.

SQL Server data warehouse and mission critical environments will often need to scale beyond the 8 memory-to-vCore ratio. For medium environments, you might 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 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:

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 TB 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 TB to 16 TB, 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 TB of memory, 1,792 vCPUs, up to 200,000 IOPS and 8,000 MBps of remote storage throughput.

Warning

Installing SQL Server to systems that exceed 64 cores per NUMA node is not currently supported. This limitation currently applies to the following Azure Virtual Machine sizes within the Msv3 and Mdsv3 Medium Memory Series: Standard_M176s_3_v3, Standard_M176s_4_v3, Standard_M176ds_3_v3, Standard_M176ds_4_v3

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