Performance guidelines for SQL Server on Azure Virtual Machines

APPLIES TO: SQL Server on Azure VM

This article provides guidance for optimizing SQL Server performance in Microsoft Azure Virtual Machines.


While running SQL Server on Azure Virtual Machines, we recommend that you continue using the same database performance tuning options that are applicable to SQL Server in on-premises server environments. However, the performance of a relational database in a public cloud depends on many factors such as the size of a virtual machine, and the configuration of the data disks.

SQL Server images provisioned in the Azure portal follow general storage configuration best practices. After provisioning, consider applying other optimizations discussed in this article. Base your choices on your workload and verify through testing.


There is typically a trade-off between optimizing for costs and optimizing for performance. This article 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 optimization listed below. Consider your performance needs, costs, and workload patterns as you evaluate these recommendations.

Quick checklist

The following is a quick checklist for optimal performance of SQL Server on Azure Virtual Machines:

Area Optimizations
VM size - Use VM sizes with 4 or more vCPU like the Standard_M8-4ms, the E4ds_v4, or the DS12_v2 or higher.

- Use memory optimized virtual machine sizes for the best performance of SQL Server workloads.

- The DSv2 11-15, Edsv4 series, the M-, and the Mv2- series offer the optimal memory-to-vCore ratio required for OLTP workloads. Both M series VMs offer the highest memory-to-vCore ratio required for mission critical workloads and is also ideal for data warehouse workloads.

- A higher memory-to-vCore ratio may be required for mission critical and data warehouse workloads.

- Leverage the Azure Virtual Machine marketplace images as the SQL Server settings and storage options are configured for optimal SQL Server performance.

- Collect the target workload's performance characteristics and use them to determine the appropriate VM size for your business.
Storage - For detailed testing of SQL Server performance on Azure Virtual Machines with TPC-E and TPC_C benchmarks, refer to the blog Optimize OLTP performance.

- Use premium SSDs for the best price/performance advantages. Configure Read only cache for data files and no cache for the log file.

- Use Ultra Disks if less than 1-ms storage latencies are required by the workload. See migrate to ultra disk to learn more.

- Collect the storage latency requirements for SQL Server data, log, and Temp DB files by monitoring the application before choosing the disk type. If < 1-ms storage latencies are required, then use Ultra Disks, otherwise use premium SSD. If low latencies are only required for the log file and not for data files, then provision the Ultra Disk at required IOPS and throughput levels only for the log File.

- Standard storage is only recommended for development and test purposes or for backup files and should not be used for production workloads.

- Keep the storage account and SQL Server VM in the same region.

- Disable Azure geo-redundant storage (geo-replication) on the storage account.
Disks - Use a minimum of 2 premium SSD disks (1 for log file and 1 for data files).

- For workloads requiring < 1-ms IO latencies, enable write accelerator for M series and consider using Ultra SSD disks for Es and DS series.

- Enable read only caching on the disk(s) hosting the data files.

- Add an additional 20% premium IOPS/throughput capacity than your workload requires when configuring storage for SQL Server data, log, and TempDB files

- Avoid using operating system or temporary disks for database storage or logging.

- Do not enable caching on disk(s) hosting the log file. Important: Stop the SQL Server service when changing the cache settings for an Azure Virtual Machines disk.

- Stripe multiple Azure data disks to get increased storage throughput.

- Format with documented allocation sizes.

- Place TempDB on the local SSD D:\ drive for mission critical SQL Server workloads (after choosing correct VM size). If you create the VM from the Azure portal or Azure quickstart templates and place Temp DB on the Local Disk, then you do not need any further action; for all other cases follow the steps in the blog for Using SSDs to store TempDB to prevent failures after restarts. If the capacity of the local drive is not enough for your Temp DB size, then place Temp DB on a storage pool striped on premium SSD disks with read-only caching.
I/O - Enable database page compression.

- Enable instant file initialization for data files.

- Limit autogrowth of the database.

- Disable autoshrink of the database.

- Move all databases to data disks, including system databases.

- Move SQL Server error log and trace file directories to data disks.

- Configure default backup and database file locations.

- Enable locked pages in memory.

- Evaluate and apply the latest cumulative updates for the installed version of SQL Server.
Feature-specific - Back up directly to Azure Blob storage.

- Use file snapshot backups for databases larger than 12 TB.

- Use multiple Temp DB files, 1 file per core, up to 8 files.

- Set max server memory at 90% or up to 50 GB left for the Operating System.

- Enable soft NUMA.

For more information on *how* and *why* to make these optimizations, please review the details and guidance provided in the following sections.

Getting started

If you are creating a new SQL Server on Azure VM and are not migrating a current source system, create your new SQL Server VM based on your vendor requirements. The vendor requirements for a SQL Server VM are the same as what you would deploy on-premises.

If you are 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.

The recommended minimum for a production OLTP environment is 4 vCore, 32 GB of memory, and a memory-to-vCore ratio of 8. For new environments, start with 4 vCore machines and scale to 8, 16, 32 vCores or more when your data and compute requirements change. For OLTP throughput, target SQL Server VMs that have 5000 IOPS for every vCore.

Use the SQL Server VM marketplace images with the storage configuration in the portal. This will make 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 section 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 core-to-memory ratio, and a 32 core-to-memory 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 strong options for larger data warehouse environments.

VM size guidance

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. Bring your core license to Azure to take advantage of the Azure Hybrid Benefit and save on SQL Server licensing costs.

Microsoft recommends a memory-to-vCore ratio of 8 as a starting point for production SQL Server workloads. Smaller ratios are acceptable for non-production workloads.

Choose a memory optimized, general purpose, storage optimized, or constrained vCore virtual machine size that is most optimal for SQL Server performance based on your workload (OLTP or data warehouse).

Memory optimized

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

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.

The Standard_M64ms has a 28 memory-to-vCore ratio for example.

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.


The Edsv4-series is designed for memory-intensive applications. These VMs have a large local storage SSD capacity, strong local disk IOPS, up to 504 GiB of RAM, and improved compute compared to the previous Ev3/Esv3 sizes with Gen2 VMs. There is a nearly consistent memory-to-vCore ratio of 8 across these virtual machines, which is ideal for standard SQL Server workloads.

This VM series is ideal for memory-intensive enterprise applications and applications that benefit from low latency, high-speed local storage.

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

DSv2-series 11-15

The DSv2-series 11-15 has the same memory and disk configurations as the previous D-series. This series has a consistent memory-to-CPU ratio of 7 across all virtual machines.

The DSv2-series 11-15 supports premium storage and premium storage caching, which is strongly recommended for optimal performance.

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 is 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 GB of memory.

Ddsv4 series

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

The Ddsv4 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 is a standard memory-to-vCore ratio of 4 across all of the virtual machines in this series.

For this reason, it is recommended to leverage the D8ds_v4 as the starter virtual machine in this series, which has 8 vCores and 32 GBs of memory. The largest machine is the D64ds_v4, which has 64 vCores and 256 GBs of memory.

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


The Ddsv4-series does not have the memory-to-vCore ratio of 8 that is recommended for SQL Server workloads. As such, considering using these virtual machines for smaller application and development workloads only.


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


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.


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 16GBs of memory), Standard_A4m_v2 (4 vCores and 32GBs of memory), and the Standard_A8m_v2 (8 vCores and 64GBs 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.


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. This virtual machine series is intended for big data scenarios, data warehousing, and large transactional databases.


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 source 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 is 1.92 TB of NVMe SSD. This means for the largest VM of this series, the L80s_v2, there is 80 vCPU and 640 BiB of memory with 10x1.92TB of NVMe storage. There is 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 restart your virtual machine.

The Lsv2 and Ls series support premium storage, but not premium storage caching. The creation of a local cache to increase IOPs is not supported.


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, IO, 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, will achieve higher memory-to-vCore ratios.

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, IO, 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 machine will be the same.


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

Storage guidance

For detailed testing of SQL Server performance on Azure Virtual Machines with TPC-E and TPC-C benchmarks, refer to the blog Optimize OLTP performance.

Azure blob cache with premium SSDs is recommended for all production workloads.


Standard HDDs and SSDs have varying latencies and bandwidth and are only recommended for dev/test workloads. Production workloads should use premium SSDs.

In addition, we recommend that you create your Azure storage account in the same data center as your SQL Server virtual machines to reduce transfer delays. When creating a storage account, disable geo-replication as consistent write order across multiple disks is not guaranteed. Instead, consider configuring a SQL Server disaster recovery technology between two Azure data centers. For more information, see High Availability and Disaster Recovery for SQL Server on Azure Virtual Machines.

Disks guidance

There are three main disk types on Azure virtual machines:

  • OS disk: When you create an Azure virtual machine, the platform will attach at least one disk (labeled as the C drive) to the VM for your operating system disk. This disk is a VHD stored as a page blob in storage.
  • Temporary disk: Azure virtual machines contain another disk called the temporary disk (labeled as the D: drive). This is a disk on the node that can be used for scratch space.
  • Data disks: You can also attach additional disks to your virtual machine as data disks, and these will be stored in storage as page blobs.

The following sections describe recommendations for using these different disks.

Operating system disk

An operating system disk is a VHD that you can boot and mount as a running version of an operating system and is labeled as the C drive.

Default caching policy on the operating system disk is Read/Write. For performance sensitive applications, we recommend that you use data disks instead of the operating system disk. See the section on Data Disks below.

Temporary disk

The temporary storage drive, labeled as the D drive, is not persisted to Azure Blob storage. Do not store your user database files or user transaction log files on the D: drive.

Place TempDB on the local SSD D:\ drive for mission critical SQL Server workloads (after choosing correct VM size). If you create the VM from the Azure portal or Azure quickstart templates and place Temp DB on the Local Disk, then you do not need any further action; for all other cases follow the steps in the blog for Using SSDs to store TempDB to prevent failures after restarts. If the capacity of the local drive is not enough for your Temp DB size, then place Temp DB on a storage pool striped on premium SSD disks with read-only caching.

For VMs that support premium SSDs, you can also store TempDB on a disk that supports premium SSDs with read caching enabled.

Data disks

  • Use premium SSD disks for data and log files: If you are not using disk striping, use two premium SSD disks where one disk contains the log file and the other contains the data. Each premium SSD provides a number of IOPS and bandwidth (MB/s) depending on its size, as depicted in the article, Select a disk type. If you are using a disk striping technique, such as Storage Spaces, you achieve optimal performance by having two pools, one for the log file(s) and the other for the data files. However, if you plan to use SQL Server failover cluster instances (FCI), you must configure one pool, or utilize premium file shares instead.



    When you provision a SQL Server VM in the portal, you have the option of editing your storage configuration. Depending on your configuration, Azure configures one or more disks. Multiple disks are combined into a single storage pool with striping. Both the data and log files reside together in this configuration. For more information, see Storage configuration for SQL Server VMs.

  • Disk striping: For more throughput, you can add additional data disks and use disk striping. To determine the number of data disks, you need to analyze the number of IOPS and bandwidth required for your log file(s), and for your data and TempDB file(s). Notice that different VM sizes have different limits on the number of IOPs and bandwidth supported, see the tables on IOPS per VM size. Use the following guidelines:

    • For Windows 8/Windows Server 2012 or later, use Storage Spaces with the following guidelines:

      1. Set the interleave (stripe size) to 64 KB (65,536 bytes) for OLTP workloads and 256 KB (262,144 bytes) for data warehousing workloads to avoid performance impact due to partition misalignment. This must be set with PowerShell.
      2. Set column count = number of physical disks. Use PowerShell when configuring more than 8 disks (not Server Manager UI).

      For example, the following PowerShell creates a new storage pool with the interleave size to 64 KB and the number of columns equal to the amount of physical disk in the storage pool:

      $PhysicalDisks = Get-PhysicalDisk | Where-Object {$_.FriendlyName -like "*2" -or $_.FriendlyName -like "*3"}
      New-StoragePool -FriendlyName "DataFiles" -StorageSubsystemFriendlyName "Storage Spaces*" `
          -PhysicalDisks $PhysicalDisks | New- VirtualDisk -FriendlyName "DataFiles" `
          -Interleave 65536 -NumberOfColumns $PhysicalDisks .Count -ResiliencySettingName simple `
          –UseMaximumSize |Initialize-Disk -PartitionStyle GPT -PassThru |New-Partition -AssignDriveLetter `
          -UseMaximumSize |Format-Volume -FileSystem NTFS -NewFileSystemLabel "DataDisks" `
          -AllocationUnitSize 65536 -Confirm:$false 
    • For Windows 2008 R2 or earlier, you can use dynamic disks (OS striped volumes) and the stripe size is always 64 KB. This option is deprecated as of Windows 8/Windows Server 2012. For information, see the support statement at Virtual Disk Service is transitioning to Windows Storage Management API.

    • If you are using Storage Spaces Direct (S2D) with SQL Server Failover Cluster Instances, you must configure a single pool. Although different volumes can be created on that single pool, they will all share the same characteristics, such as the same caching policy.

    • Determine the number of disks associated with your storage pool based on your load expectations. Keep in mind that different VM sizes allow different numbers of attached data disks. For more information, see Sizes for virtual machines.

    • If you are not using premium SSDs (dev/test scenarios), the recommendation is to add the maximum number of data disks supported by your VM size and use disk striping.

  • Caching policy: Note the following recommendations for caching policy depending on your storage configuration.

    • If you are using separate disks for data and log files, enable read caching on the data disks hosting your data files and TempDB data files. This can result in a significant performance benefit. Do not enable caching on the disk holding the log file as this causes a minor decrease in performance.

    • If you are using disk striping in a single storage pool, most workloads will benefit from read caching. If you have separate storage pools for the log and data files, enable read caching only on the storage pool for the data files. In certain heavy write workloads, better performance might be achieved with no caching. This can only be determined through testing.

    • The previous recommendations apply to premium SSDs. If you are not using premium SSDs, do not enable any caching on any data disks.

    • For instructions on configuring disk caching, see the following articles. For the classic (ASM) deployment model see: Set-AzureOSDisk and Set-AzureDataDisk. For the Azure Resource Manager deployment model, see: Set-AzOSDisk and Set-AzVMDataDisk.


      Stop the SQL Server service when changing the cache setting of Azure Virtual Machines disks to avoid the possibility of any database corruption.

  • NTFS allocation unit size: When formatting the data disk, it is recommended that you use a 64-KB allocation unit size for data and log files as well as TempDB. If TempDB is placed on the temporary disk (D:\ drive) the performance gained by leveraging this drive outweighs the need for a 64-KB allocation unit size.

  • Disk management best practices: When removing a data disk or changing its cache type, stop the SQL Server service during the change. When the caching settings are changed on the OS disk, Azure stops the VM, changes the cache type, and restarts the VM. When the cache settings of a data disk are changed, the VM is not stopped, but the data disk is detached from the VM during the change and then reattached.


    Failure to stop the SQL Server service during these operations can cause database corruption.

I/O guidance

  • The best results with premium SSDs are achieved when you parallelize your application and requests. Premium SSDs are designed for scenarios where the IO queue depth is greater than 1, so you will see little or no performance gains for single-threaded serial requests (even if they are storage intensive). For example, this could impact the single-threaded test results of performance analysis tools, such as SQLIO.

  • Consider using database page compression as it can help improve performance of I/O intensive workloads. However, the data compression might increase the CPU consumption on the database server.

  • Consider enabling instant file initialization to reduce the time that is required for initial file allocation. To take advantage of instant file initialization, you grant the SQL Server (MSSQLSERVER) service account with SE_MANAGE_VOLUME_NAME and add it to the Perform Volume Maintenance Tasks security policy. If you are using a SQL Server platform image for Azure, the default service account (NT Service\MSSQLSERVER) isn’t added to the Perform Volume Maintenance Tasks security policy. In other words, instant file initialization is not enabled in a SQL Server Azure platform image. After adding the SQL Server service account to the Perform Volume Maintenance Tasks security policy, restart the SQL Server service. There could be security considerations for using this feature. For more information, see Database File Initialization.

  • Be aware that autogrow is considered to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow. If autogrow is used, pre-grow the file using the Size switch.

  • Make sure autoshrink is disabled to avoid unnecessary overhead that can negatively affect performance.

  • Move all databases to data disks, including system databases. For more information, see Move System Databases.

  • Move SQL Server error log and trace file directories to data disks. This can be done in SQL Server Configuration Manager by right-clicking your SQL Server instance and selecting properties. The error log and trace file settings can be changed in the Startup Parameters tab. The Dump Directory is specified in the Advanced tab. The following screenshot shows where to look for the error log startup parameter.

    SQL ErrorLog Screenshot

  • Set up default backup and database file locations. Use the recommendations in this article, and make the changes in the Server properties window. For instructions, see View or Change the Default Locations for Data and Log Files (SQL Server Management Studio). The following screenshot demonstrates where to make these changes.

    SQL Data Log and Backup files

  • Enable locked pages to reduce IO and any paging activities. For more information, see Enable the Lock Pages in Memory Option (Windows).

  • If you are running SQL Server 2012, install Service Pack 1 Cumulative Update 10. This update contains the fix for poor performance on I/O when you execute select into temporary table statement in SQL Server 2012. For information, see this knowledge base article.

  • Consider compressing any data files when transferring in/out of Azure.

Feature-specific guidance

Some deployments may achieve additional performance benefits using more advanced configuration techniques. The following list highlights some SQL Server features that can help you to achieve better performance:

Back up to Azure Storage

When performing backups for SQL Server running in Azure Virtual Machines, you can use SQL Server Backup to URL. This feature is available starting with SQL Server 2012 SP1 CU2 and recommended for backing up to the attached data disks. When you backup/restore to/from Azure Storage, follow the recommendations provided at SQL Server Backup to URL Best Practices and Troubleshooting and Restoring from Backups Stored in Azure Storage. You can also automate these backups using Automated Backup for SQL Server on Azure Virtual Machines.

Prior to SQL Server 2012, you can use SQL Server Backup to Azure Tool. This tool can help to increase backup throughput using multiple backup stripe targets.

SQL Server Data Files in Azure

This new feature, SQL Server Data Files in Azure, is available starting with SQL Server 2014. Running SQL Server with data files in Azure demonstrates comparable performance characteristics as using Azure data disks.

Failover cluster instance and Storage Spaces

If you are using Storage Spaces, when adding nodes to the cluster on the Confirmation page, clear the check box labeled Add all eligible storage to the cluster.

Uncheck eligible storage

If you are using Storage Spaces and do not uncheck Add all eligible storage to the cluster, Windows detaches the virtual disks during the clustering process. As a result, they do not appear in Disk Manager or Explorer until the storage spaces are removed from the cluster and reattached using PowerShell. Storage Spaces groups multiple disks in to storage pools. For more information, see Storage Spaces.

Multiple instances

Consider the following best practices when deploying multiple SQL Server instances to a single virtual machine:

  • Set the max server memory for each SQL Server instance, ensuring there is memory left over for the operating system. Be sure to update the memory restrictions for the SQL Server instances if you change how much memory is allocated to the virtual machine.
  • Have separate LUNs for data, logs, and TempDB since they all have different workload patterns and you do not want them impacting each other.
  • Thoroughly test your environment under heavy production-like workloads to ensure it can handle peak workload capacity within your application SLAs.

Signs of overloaded systems can include, but are not limited to, worker thread exhaustion, slow response times, and/or stalled dispatcher system memory.

Collect performance baseline

For a more prescriptive approach, gather performance counters using PerfMon/LogMan and capture SQL Server wait statistics to better understand general pressures and potential bottlenecks of the source environment.

Start by collecting the CPU, memory, IOPS, throughput, and latency of the source workload at peak times following the application performance checklist.

Gather data during peak hours such as workloads during your typical business day, but also other high load processes such as end-of-day processing, and weekend ETL workloads. Consider scaling up your resources for atypically heavily workloads, such as end-of-quarter processing, and then scale done once the workload completes.

Use the performance analysis to select the VM Size that can scale to your workload's performance requirements.

IOPS and Throughput

SQL Server performance depends heavily on the I/O subsystem. Unless your database fits into physical memory, SQL Server constantly brings database pages in and out of the buffer pool. The data files for SQL Server should be treated differently. Access to log files is sequential except when a transaction needs to be rolled back where data files, including TempDB, are randomly accessed. If you have a slow I/O subsystem, your users may experience performance issues such as slow response times and tasks that do not complete due to time-outs.

The Azure Marketplace virtual machines have log files on a physical disk that is separate from the data files by default. The TempDB data files count and size meet best practices and are targeted to the ephemeral D:/ drive..

The following PerfMon counters can help validate the IO throughput required by your SQL Server:

  • \LogicalDisk\Disk Reads/Sec (read and write IOPS)
  • \LogicalDisk\Disk Writes/Sec (read and write IOPS)
  • \LogicalDisk\Disk Bytes/Sec (throughput requirements for the data, log, and TempDB files)

Using IOPS and throughput requirements at peak levels, evaluate VM sizes that match the capacity from your measurements.

If your workload requires 20 K read IOPS and 10K write IOPS, you can either choose E16s_v3 (with up to 32 K cached and 25600 uncached IOPS) or M16_s (with up to 20 K cached and 10K uncached IOPS) with 2 P30 disks striped using Storage Spaces.

Make sure to understand both throughput and IOPS requirements of the workload as VMs have different scale limits for IOPS and throughput.


Track both external memory used by the OS as well as the memory used internally by SQL Server. Identifying pressure for either component will help size virtual machines and identify opportunities for tuning.

The following PerfMon counters can help validate the memory health of a SQL Server virtual machine:

Compute / Processing

Compute in Azure is managed differently than on-premises. On-premises servers are built to last several years without an upgrade due to the management overhead and cost of acquiring new hardware. Virtualization mitigates some of these issues but applications are optimized to take the most advantage of the underlying hardware, meaning any significant change to resource consumption requires rebalancing the entire physical environment.

This is not a challenge in Azure where a new virtual machine on a different series of hardware, and even in a different region, is easy to achieve.

In Azure, you want to take advantage of as much of the virtual machines resources as possible, therefore, Azure virtual machines should be configured to keep the average CPU as high as possible without impacting the workload.

The following PerfMon counters can help validate the compute health of a SQL Server virtual machine:

  • \Processor Information(_Total)% Processor Time
  • \Process(sqlservr)% Processor Time


Ideally, try to aim for using 80% of your compute, with peaks above 90% but not reaching 100% for any sustained period of time. Fundamentally, you only want to provision the compute the application needs and then plan to scale up or down as the business requires.

Next steps

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.