Best Practices for Configuring SQL on Azure IaaS VM
Running SQL Server workloads on Azure as IAAS (Infrastructure as a service) is becoming popular. With more SQL Servers running on Azure virtual machines, there has been a need to know how to effectively and efficiently one can run them with minimal bottlenecks.
In order to have the maximum performance out of your Azure VM’s, here are the list of steps to look out for:
- A minimum of A2 Azure VM is recommended for running SQL workloads.
- Ensure that the storage account and the VM are located in the same region to reduce any network latency
- Azure Geo-Replication should be disabled at the storage level. This ensures maximum IOPS for SQL Server to use.
- Do not use the “TEMP” storage for storing any SQL Server files like Log files and Data files.
- Disable Data Disk Caching (caching policy = None)
- Configure RAID or striping using multiple Data Disks for maximum Read-Write performance.
- Format the Disk’s in 64 KB chunks. Do not use the default “File Allocation Units”
- Ensure seperate disks for Log files (ldf’s) and Data files (mdf’s). This will get dedicate IOPS for both Data and log files.
Less than 10 ms – very good
Between 10 – 20 ms – okay
Between 20 – 50 ms – slow, needs attention
Greater than 50 ms – Serious I/O bottleneck
- You need 2 ms – 5 ms of response time for the SQL Server Log files.
- Enable “Page Compression” for Databases. Do not enable this feature if you have high CPU usage.
- Disable “Autogrowth” for Databases & Tembdb
- Do not keep the Database files on the default location. Move them to a different disk drive. This includes “System Databases”.
- SQL Server Error Log’s and Trace Files should be pointing to a different Data Disk.
- Enable “Lock Pages in Memory” feature. This is only applicable for SQL Server Enterprise Editions only.
- Se Backup jobs to be saves directly on Azure Storage Blob’s.
- Enable and use “Buffer Pool Extension” on dedicated premium Azure Storage. These are SSD Disks and will give you close to 400 MB/s of throughput.
- Configure Tempdb on SSD disks where possible.
Hope this covers the basics of what steps needs to be followed in order to run SQL Server more efficiently on Azure VM’s.