Performance Considerations for SQL Server on Azure VMs

The performance considerations for running a SQL Server on Azure VMs is no different from SQL Server running on a box but in addition there are various other optimizations or considerations available in Azure Infrastructure which you need to be aware of and leverage to give you a predictable performance at reduced cost. In this chapter we will discuss these performance best practices and considerations for running SQL Server on Azure VMs.

Storage Considerations

One of the major difference when running SQL Server on Azure VMs is, you no longer have storage admins to work with which can be good and bad since you can now take control of the storage yourself and have no dependencies on storage team, but now, if the storage doesn’t give you the best performance you do not have anyone else to blame for as well. In this section, we will discuss the various storage considerations and best practices which you need to be aware to give you an optimized performance in Azure.

Be aware of Storage account Limits

As mentioned earlier, Azure is a shared multi-tenant environment and hence all the services on Azure has limitations to ensure each account gets a dedicated predictable storage and compute performance without impacting other accounts. Storage account acts a container which stores the operating system disk, temporary disks, data disks as well as Azure blob storage. There are limitations on the maximum number of IOPs and ingress/egress limits per storage account. The Ingress refers to all the data sent into the storage account while Egress refers to all the data extracted from the storage account.

The following table provides the list of Standard and Premium storage account limitations

Standard Storage Limits

Table 3: Standard Storage Account Limits

Premium Storage Account Limits

Note: The above limitations are as of today and might change in future. The above table and up-to-date limitations on Storage Account and other Azure services can be found in the following article
https://azure.microsoft.com/en-us/documentation/articles/azure-subscription-service-limits/#storagelimits

As seen in the previous table, the total request rate supported by a storage account is 20,000 IOPs. Hence if we have a VM which is running disk at full capacity, maximum number of disks from Standard storage account which can attached to the VM is 40 (20000/500 IOPs), to give you the desired performance.  However this is not a hard limitation so if you exceed this limitation, the storage account throttling can kick in and it can have a negative performance impact on your VMs.

Hence it is recommended to use multiple separate storage account depending on the workloads to avoid throttling and negative impact on performance of SQL Server due to storage latencies. Further it is important that storage account is in the same location as the VMs to obtain the best performance with storage.

Disable Geo-redundancy on Storage accounts

Azure storage account supports Geo-redundancy to replicate the storage contents across multiple datacenters across regions to provide HA/DR and high performance for Content Delivery Network (CDN) kind of application. However Geo-redundancy in storage account doesn’t guarantee write-consistency across disks which makes it practically unusable for SQL Server as High Availability and Disaster Recovery solution in azure since it breaks the Write Ahead Logging (WAL) protocol for SQL Server and results in corruption in the database on the DR site.

Since Geo-redundancy is unusable for SQL Server workloads it would be beneficial to disable Geo-redundancy for storage accounts hosting the Data disks for SQL Server to avoid replication overhead and reduce the per-minute costs for storage accounts. Hence the recommended options for creating a storage account for SQL Server is locally redundant storage (LRS)  which replicates the storage content locally in Azure datacenters to 3 redundant copies for better availability and reliability. The storage account can be created from Azure portal or PowerShell.

Use Premium Storage

As of today, Microsoft Azure provides two types of storage offerings namely

• Standard Storage
• Premium Storage

Azure Standard Storage supports up to 500 IOPs per Data Disk attached to the VMs. The IOPs supported by Azure Standard Storage Data Disk can be further bumped up by adding multiple disk to Storage Pool which acts a software RAID configuration. However the Standard Storage disks doesn’t provide low latency desired for running SQL Server workloads. The high latency of the disk makes it an unattractive offering for running SQL Server workloads since the SQL Server inherently is I/O intensive workload with high reads/write for reading and writing the data into the database and log files.

Azure Premium Storage supports up to 50k IOPs per Data Disk attached to the VMs. Moreover it supports high IOPs at reduced Disk latency which makes it a preferred choice for High Performance I/O intensive workload running on Azure Virtual Machines and more specifically for SQL Server running on Azure VMs.

In order to use Premium storage, you need to create a Premium storage account which can be created from Azure portal or PowerShell. Azure uses the storage account as a container for your operating system (OS) and data disks. If you want to use a Premium Storage account for your VM disks, you need to use the DS-series of VMs. You can use both Standard and Premium storage disks with DS-series of VMs. But you cannot use Premium Storage disks with non-DS-series of VMs. The amount of IOPs and bandwidth supported by Premium storage data disks depends on the Premium storage disk types. As of today, Premium storage supports three types of disk types namely P10, P20 and P30. The IOP and bandwidth supported by each of these types is listed in the following table.

Table 4: Premium Storage Account Disk Types

Note: The Throughput and IOPs supported by a Disk in a VM is also dependent on the size of the VM (DS0-DS14). Hence it is important to ensure and choose a VM size which supports the sufficient bandwidth to run these disk at their maximum capacity

Further details on the Azure Premium Storage can be found in the article which is maintained up-to-date by Microsoft

https://azure.microsoft.com/en-us/documentation/articles/storage-premium-storage-preview-portal/#using-premium-storage-for-disks

Use separate disks for data, log files and Tempdb

For SQL Server workloads, it is recommended to separate disks for data files and log files due to the nature of the IO (random read/write IO versus sequentially write IO respectively). Hence when using premium storage, the recommendation for SQL Server workloads running on Azure VMs is at minimum 2 X P30 disks to be attached to the VMs dedicated for User data files and log files. When using Standard storage with storage pools discussed in the following section, it is recommended to create at least 2 storage pools to separate the IO workload for data and log files. Further operating system disks and temporary disk shouldn’t be used for storing any database files (including system databases except tempdb which is discussed in the following section), backups, error logs or tracing files. In other words, OS disk should not be used to host any SQL Server files except for the SQL Server binaries.

Use Temporary disk to store tempdb or BPE in D or G-series VMs

D and G-series VMs have a temporary drive labeled as D: along with OS drive attached to the VM when the VM is provisioned. It is called as temporary disk since the data stored in the disk is not persistent and lost on every reboot of the VM. The temporary disk is High performance, low latency SSD drive but it cannot be used to storage user data or log files since the data is not persistent. However the temporary disk can be used for storing tempdb or Buffer Pool extension (BPE) files which gets cleaned up or recreated on every restart of SQL Server service.

Tempdb is one of the highly accessed database files in a SQL Server instance since most queries require tempdb for sorting, hashing, versioning or storing temp tables etc. Hence moving tempdb on SSD disks would improve the overall performance of the SQL Server workload especially the ones that use tempdb objects heavily.

The detailed steps to move tempdb and BPE files to temporary disk in a D-series VM is documented in the following blog by the Microsoft Product Team.

http://blogs.technet.com/b/dataplatforminsider/archive/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions.aspx

Using Storage Spaces for Data Disks in Standard Storage

As discussed in previous section, it is recommended to use premium storage accounts for configuring SQL Server on Azure VMs. However due to cost or any other reasons, if you would like to use Standard storage for configuring data disks for SQL Server VMs, it is recommended to use Storage Spaces to stripe the data across multiple data disks. The advantage of creating Storage Pools and using Storage spaces is, it allows you to exceed the IOPs limitation available per data disk. In Standard storage, the max IOPs supported per data disks is 500 IOPs however you can stripe 4 data disk together in a Storage pool to support close to 2000 IOPs per LUN. The storage pool improves the overall IO throughput as well as the latency of the storage to some extent.

It is however important to note that Storage Spaces was introduced starting Windows8/Windows Server 2012, hence only VMs running Windows 8/2012 would support creation of Storage Pools and Storage Spaces to stripe across multiple data disks.

For SQL Server workloads, it is recommended to create at minimum two storage pools per VMs namely one for data files and other to host the log files. 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. To configure a Storage Space with more than 8 disks you must use PowerShell (not Server Manager UI) to explicitly set the number of columns to match the number of disks. For more information on how to configure Storage Spaces, see Storage Spaces Cmdlets in Windows PowerShell.

The following PowerShell code snippet can be used for the creating storage pools in SQL Server Azure VMs. The following code shows the creation of two storage pools each striped across 2 disk each with stripe size of 64KB. The storage space are labelled as DataDisks and LogDisks to identify them as drives for data and log files respectively.

Code Listing 16: Creating Storage Pool for SQL Server Data and Log Files in Azure VM

Caching Policy for disks

If you are using Premium storage for your data and log files, it is recommended to disable caching for disks hosting the log files and enable read caching on the disks hosting data files and tempdb. With Standard storage, it is recommended to disable caching for all the data disks.

The default caching policy for OS disk is read/write which doesn’t need to be changed. It is highly recommended you do not store any database files including the system databases, error logs, backup and tracing files on OS disk.

NTFS allocation unit size

All the disks attached to the VMs running SQL Server should be formatted with NTFS with NTFS allocation unit size of 64 KB which is recommended for SQL Server data and log files including tempdb.

Choose the Right VM size for your workload

As discussed earlier, the VM size decides the compute and storage capacity supported by the VM. In Azure, VMs are available in two tiers – basic and standard. Both types offer a choice of sizes, but the basic tier doesn’t provide some capabilities available with the standard tier, such as load-balancing and auto-scaling. The standard tier supports VM from A-series, D-series, DS-series and G-series discussed earlier. The detailed sizes of VM available in Azure is documented in the following article by Microsoft

https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-size-specs/

SQL Server on Azure is recommended to use Premium storage for better performance. As discussed earlier if you need to use Premium storage for your VMs, you need to use DS-series VMs. DS-series VMs supports attaching disk from both Premium storage account as well as Standard Storage which makes it as a preferred choice for installing and configuring SQL Server. Even if you are planning to attach Standard storage to the VMs, it might make more sense to use DS-series VMs since if you plan to upgrade to Premium storage in future, DS-series VMs would allow and migrating to Premium storage with minimal efforts as opposed to A-series or D-series VMs which doesn’t support Premium storage at all and would involve rebuilding of the entire VM and increase the migration efforts and cost.

Hence for SQL Server workloads it is recommended to choose Standard tier with DS-series VM. The generic recommendation is running VM size of DS3 or higher for SQL Enterprise edition and DS2 or higher for SQL Standard Edition.

The size you choose in DS-series VMs (DS1 – DS14) depends on the Application workloads, throughput requirements and the resource utilization on the VM. The best part of running the VM on the cloud is you can always start with the smaller VM size and scale up to higher size with minimal effort, cost and downtime.

Use SQL Server Best Practices

Most of performance recommendations or considerations for running a SQL Server in Azure VMs is surrounded around storage and IO optimization since storage is the major performance differentiator between running a SQL Server on premise versus running the SQL Server in Azure VMs. In this section, we will discuss some of the known SQL Server Best practice configurations which optimizes the IO thereby helps in optimizing the overall performance of SQL workload running in Azure VMs.

Use Lock Pages in Memory

Lock Pages in Memory is a system security privilege which is not assigned to any users including Administrators by default. This is because any application or service which runs with an account with this privilege can lock their page in memory and even OS cannot page out these buffers from Memory. For SQL Server workloads, it is desired that the SQL Buffer Pool pages be locked in Memory and shouldn’t be page out by OS to give us a predictable performance. Hence it is recommended to assign Lock Page in Memory privilege to SQL service startup account. However when using Lock Pages in Memory privilege, it is critical to cap the max server memory of SQL Server instance to around 70-80% of total memory on the VM to leave breathing space for OS and other applications or services (monitoring agents, backup software, SQL Management Studio) on the VM.

You can use the following steps to enable Lock Pages in Memory privilege for SQL Server service account

1. On the Start menu, click Run. In the Open box, type gpedit.msc.
2. On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
3. Expand Security Settings, and then expand Local Policies.
4. Select the User Rights Assignment folder.
5. The policies will be displayed in the details pane.
6. In the pane, double-click Lock pages in memory.
7. In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
8. In the Select Users, Service Accounts, or Groups dialog box, add an account with privileges to run sqlservr.exe.
9. Log out and then log back in for this change to take effect.

Note: On the VM server, if you assign Lock Pages in Memory privilege to SQL service account and if you are running any other service using the same account, the memory pages committed by the other service is also locked and cannot be paged out. Hence from security and administrative standpoint, it is recommended to keep SQL service account dedicated and exclusive for running SQL Server service.

Enable Instant File Initialization for data files

Instant File initialization was first introduced by Microsoft SQL Product Team in SQL 2005 to reduce the time required for initial file allocation or growth for data files when the database is created or grown either manually or automatically. Instant File initialization skips the zeroing of the files which is an IO intensive operation and reduces the overall time for new file allocation significantly. To take advantage of instant file initialization, you need to grant the SQL Server (MSSQLSERVER) service account with SE_MANAGE_VOLUME_NAME and add it to the Perform Volume Maintenance Tasks security policy. When we provision a SQL Server image from Azure Gallery, this privilege is not automatically assigned to the SQL service account and hence it needs to be manually assigned as a post-configuration task after provisioning the SQL Server image.

You need to follow the following steps to grant an account the Perform volume maintenance tasks permission:

1. On the computer where the backup file will be created, open the Local Security Policy application (secpol.msc).
2. In the left pane, expand Local Policies, and then click User Rights Assignment.
3. In the right pane, double-click Perform volume maintenance tasks.
4. Click Add User or Group and add any user accounts that are used for backups.
5. Click Apply, and then close all Local Security Policy dialog boxes.
6. Restart SQL Server services for the permissions to take effect.

Note: Using Instant File Initialization can have security consideration. You can read the following topic from MSDN to learn more about Instant File Initialization https://msdn.microsoft.com/library/ms175935.aspx

Instant File Initialization is supported and available only for data files. Even when you assign this privilege to SQL Service account, the T-log file allocation will be IO intensive and time consuming since it will involving zeroing of files.

Disable Autoshrink and avoid Autogrow

Autoshrink is a database file property to shrink the database automatically when database is not in use. Autoshrink is an IO intensive activity which causes fragmentation of database files as well as the disks. The Autoshrink property for database is disabled by default and is recommended to be disabled and shouldn’t be used as a preferred method for shrinking the database files especially when running on Azure VMs.

Autogrow is a database file property to grow the database automatically when the database files are running out of space. Autogrow is again an IO intensive operation which can kick off randomly at any time when the data or log file is running out of space. Autogrow also causes fragmentation of the database files and disk and if it occurs during the business hours, it can stall or hang the SQL Server until the database file growth is completed. While it is not recommended to disable to Autogrow since it can work as an insurance and help you in critical situations to grow the database file when it is full. Ideally, it is recommended to have a monitoring and alerting system which alerts a DBA when a database file has around 10-15% of free space on the file left. The DBA should respond to the alert by growing the file manually during maintenance windows or off business hours to avoid autogrow. Hence Autogrow is like an insurance which should be turned ON but should be avoided.

Use database page compression

SQL Server Data compression was introduced with SQL 2008 which allows you to compress the database pages while storing it in the data files. This reduces the IO bandwidth and storage required to store the data files and thereby improves the overall performance due to reduced IO workload. SQL Server supports Row compression, Page compression and Columnstore compression (introduced in SQL 2014) each of which provides different compression ratio at the expense of higher CPU utilization.

For further details on Data Compression in SQL Server, you can refer to following article from MSDN

https://msdn.microsoft.com/library/cc280449.aspx

For SQL Server workloads running on Azure VM, it is recommended to use database compression wherever possible to reduce IO bandwidth and thereby improve the response time of the queries.

Backup to Azure Blob storage with compressed backups

SQL Server supports native backup compression starting SQL 2008 R2 which helps to reduce the size of the backups, as the backups are being performed. For SQL Server workload running on a box or Azure VM, it is recommended to turn on backup compression at the instance level which will ensure any backups from performed on the SQL instance is compressed. Further the restoration of database is not impacted much when the backups are compressed.

You can use the following t-sql code to enable compressed backups at the instance level

Code Listing 17: Enable Compressed Backups

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

Apply SQL Server performance fixes

By default, it is always recommended to run SQL Server on the latest service pack or cumulative update to cover all the known issues for the server. Specifically for SQL Server running on Azure VMs, If 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.