SALES: 1-800-867-1380

Performance Best Practices for SQL Server in Azure Virtual Machines

Updated: April 22, 2015

This topic provides best practices for optimizing SQL Server performance in Microsoft Azure Virtual Machine. While running SQL Server in 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 environment. 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.

You can get the information in this topic and more as a whitepaper download (.docx).

In this topic:

The following is a quick check list that you can follow:

  • Use minimum Standard Tier A2 for SQL Server VMs.

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

  • Disable Azure geo-replication on the storage account.

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

  • Avoid using Azure data disk caching options (caching policy = None).

  • Stripe multiple Azure data disks to get increased IO throughput.

  • Format with documented allocation sizes.

  • Separate data and log file I/O paths to obtain dedicated IOPs for data and log.

  • Enable database page compression.

  • Enable instant file initialization for data files.

  • Limit or disable autogrow on the database.

  • Disable autoshrink on the database.

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

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

  • Apply SQL Server performance fixes.

  • Setup default locations.

  • Enable locked pages.

  • Backup directly to blob storage.

For more information, please follow the guidelines provided in the following sub sections.

For performance sensitive applications, it’s recommended that you use the following virtual machines sizes:

  • SQL Server Enterprise Edition: Standard tier A3 or higher

  • SQL Server Standard Edition: Standard tier A2 or higher

For up-to-date information on supported virtual machine sizes, see Virtual Machine and Cloud Service Sizes for Azure.

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 in Azure Virtual Machines.

When you create an Azure Virtual Machine, the platform will attach at least one disk to the VM for your operating system disk. This disk is a VHD stored as a page blob in storage. You can also attach additional disks to your virtual machine as data disks, and these will be stored in storage as page blobs. There is another disk present in Azure Virtual Machines called the temporary disk. This is a disk on the node that can be used for scratch space.

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 C drive.

Default caching policy on the operating system disk is Read/Write. For performance sensitive applications, we recommend that you use the data disk instead of the operating system disk. Attach one or more data disks to your virtual machine without changing the default caching policy, which is None for data disks.

The temporary storage drive, labeled as the D: drive, is not persisted to Azure blob storage. Do not store your data or log files on the D: drive.

Only store tempdb and/or Buffer Pool Extensions on the D drive when using the D-Series Virtual Machines (VMs). Unlike the other VM series, the D drive in the D-Series VMs is SSD-based. This can improve the performance of workloads that heavily use temporary objects or that have working sets which don't fit in memory. For more information, see Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions.

  • Number of data disks: For throughput sensitive applications, we recommend that you attach maximum number of disks allowed by the VM size. Note that the latency will not improve by adding more data disks if your workload is well within the maximum IOPs limit.

    For information on maximum IOPs per disk, see Virtual Machine and Cloud Service Sizes for Azure.

  • Caching policy: On the data disk, both read and write caching is disabled by default. If you are using Premium Storage, enable read caching on the data disks only. Otherwise, use the default and do not enable any caching on the data disk. For more information on disk caching, see Manage Disks and Images.

    For instructions on configuring disk caching, see the following topics: Set-AzureOSDisk, and Set-AzureDataDisk.

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

  • Disk Striping: We recommend that you follow these guidelines:

    • For Windows 8/Windows Server 2012 or later, use Storage Spaces. Set stripe size to 64 KB for OLTP workloads and 256 KB for data warehousing workloads to avoid performance impact due to partition misalignment. In addition, set column count = number of physical disks. For more information on how to configure Storage Spaces, see Storage Spaces Cmdlets in Windows PowerShell.

    • For Windows 2008 R2 or earlier, you can use dynamic disks (OS striped volumes) and the stripe size is always 64 KB. Note that 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.

  • Data and log files placement: If your workload is not log intensive and does not need dedicated IOPs, you can configure just one storage pool. Otherwise, for VMs allowing more than 4 disks to be attached, place the data and log files on separate disks or storage pools. For Standard tier A2, we recommend a configuration of three data disks for data files and tempdb, and one data disk for log files. Depending on whether your workload is data or log sensitive, you can adjust the number of disks dedicated to data or log storage pool accordingly.

  • 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 compressing any data files when transferring in/out of Azure.

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

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

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

  • Move system databases (such as msdb and tempdb), backups and the default data and log directories of SQL Server to non-cached data disks to improve performance. Then, perform the following actions:

    • Adjust the XEvent and Trace file paths.

    • Adjust the SQL Error Log path.

    • Adjust the default backup path.

    • Adjust the default database location.

  • Establish locked pages to reduce IO and any paging activities.

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:

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft