Performance Considerations for SQL Server in Azure Virtual Machines
Updated: February 28, 2014
While running SQL Server in Windows 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.
Important Note: For most up-to-date comprehensive information, see Performance Guidance for SQL Server in Azure Virtual Machines white paper.
This topic provides some preliminary recommendations and guidelines for optimizing SQL Server performance in Windows Azure Virtual machines.
Configuring the disks for performance
When you create a Windows 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 Windows Azure Virtual Machines called the temporary disk. This is a disk on the node that can be used for scratch space.
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 C drive.
Data Files: The operating system disk has a limit of 127 GB. This disk can be considered for storing the database files for databases that are 10 GB or smaller in size.
Caching: By default, the operating system disk is configured with both read and write caching. Enabling “Read Write” cache (default setting) for the operating system disk helps improve overall operating system performance and boot times. Also, it can help reduce the read latency for workloads with smaller databases (10 GB or smaller) that require a low number of concurrent read I/Os. This is because the working set can fit into the disk cache or the memory, reducing trips to the backend Blob storage. For databases larger than 10 GB, we recommend that you use data disks. For instructions on configuring disk caching, see the following topics: Set-AzureOSDisk, Set-AzureDataDisk, and Managing Virtual Machines with the Windows Azure PowerShell Cmdlets.
The temporary storage drive, labeled as the D: drive is not persisted and is not saved in the Windows Azure Blob storage. It is used primarily for the page file and its performance is not guaranteed to be predictable. Management tasks such as a change to the virtual machine size, resets the D: drive. In addition, Windows Azure erases the data on the temporary storage drive when a virtual machine fails over. The D: drive is not recommended for storing any user or system database files, including tempdb.
Data Files: Databases that are over 10 GB in size should be placed on the data disk. Place database, log, and backup files in a single data disk when possible. Depending on the size of the files or number of databases, additional data disks might be needed. Based on your tests, you may also use additional disks if your system has an intensive I/O workload and the I/Os per second (IOPs) requirements exceed what a single data disk can provide. Review the disk striping recommendations below if you must use multiple data disks. For information on the maximum IOPs guidance for data disks, see Virtual Machine and Cloud Service Sizes for Windows Azure.
Disk Striping: If using more than one data disk, distribute data files in the database filegroups instead of using operating system disk striping. For information, see Database Files and Filegroups. You may also need to disable geo-replication and configure 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.
If more than one data disk is used for storing the files of a database, do the followings:
Distribute data files in the database filegroups instead of using operating system disk striping. For information, see Database Files and Filegroups.
Turn off geo-replication as it does not guarantee the consistency of data across multiple data disks.
- Distribute data files in the database filegroups instead of using operating system disk striping. For information, see Database Files and Filegroups.
Caching: On the data disk, both read and write caching is disabled by default. If the workload demands a high rate of random I/Os and throughput is important to you, the general guideline is to keep the cache set to the default value of disabled on the data disk. If the workload is sensitive to latency and requires low number of concurrent read I/Os, consider enabling read cache on the data disk. However, it is highly recommended that the disk configuration is tested before implementing read cache configuration for production workloads. For more information on disk caching, see Manage Disks and Images
For instructions on configuring disk caching, see the following topics: Set-AzureOSDisk, Set-AzureDataDisk, and Managing Virtual Machines with the Windows Azure PowerShell Cmdlets.
I/O Performance Considerations
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 Windows 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 Windows 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 Windows Azure platform image. After adding the SQL Server service account to the Perform Volume Maintenance Tasks security policy, restart the SQL Server service. For more information, see Database File Initialization.
Consider using the default values for the NTFS allocation unit size when you format a data disk.
Troubleshooting and Monitoring
To monitor and troubleshoot SQL Server performance in Windows Azure Virtual Machines, use the following tools:
Windows tools: SQLIO, Perfmon counters, Xperf
SQL Server tools: Dynamic Management Views, ErrorLog, Profiler, Dreplay
Windows Azure tools: Storage Analytics
When using any tools to collect information on the data drives, consider using the temporary storage disk (D) for storing transient information to avoid any additional storage transactions.
Important Note: For comprehensive information, see Performance Guidance for SQL Server in Azure Virtual Machines white paper.
Other ResourcesSQL Server in Azure Virtual Machines