3 key performance tips working with SQL Server on Azure IaaS VM

With the world moving to cloud, one of the major concerns for the SQL Server Database Administrators (DBAs) is performance of SQL Server running on virtual machine (VM) as compare to an on-premises machine. In case of on-premises machine, hardware is owned by company but on the other hand, in case of IaaS, the hardware, where the VM is running, is owned by cloud vendor. If we choose Azure VM then it would be Microsoft who would own the hardware.

When SQL is used in VM, there are chances that SQL might show slow performance if it is not configured correctly. This blog covers the tips and tricks to improve performance of SQL Server running inside a Azure VM.

Avoid using Standard Storage for high OLTP SQL Instance

There are two kind of storages available in Microsoft Azure – Standard and Premium storage. Standard storage accounts are backed by magnetic drives and provide the lowest cost per GB. They’re best for applications that require bulk storage or where data is accessed infrequently. Premium storage accounts are backed by solid state drives (SSDs) and they are known to offer consistent low latency performance.

If you are planning to deploy SQL for production use and performance is critical, then avoid using Standard Storage. Microsoft recommends migrating any virtual machine disk requiring high IOPS to Azure Premium Storage for the best performance for any application. As explained earlier, standard storage stores virtual machine disk data on Hard Disk Drives (HDDs) whereas Premium storage uses Solid State Drives (SSDs). These drives are expected to perform much better than spinning drive.

Here is the screenshot to show the place to choose the type of storage.

Use higher sized VM

Based on the documentation from Microsoft, it is recommended to use DS3 or higher sized VM for Enterprise edition of SQL Server. The usage of DS2 or higher is OK when we use Standard edition of SQL Server. When we choose predefined image from portal to deploy SQL Server, by default it recommends the VM as shown below.

Change Database/Server Level Settings

Since IO is one of the concern, we need to make sure that database settings are such that the IO can be reduced. Here are the few setting which are found to be useful.

1. Enable instant file initialization for data files. This should be done by granting “Perform Volume Maintenance Tasks” permission to service account for SQL Server. This is very useful when we try to restore a database which has huge database files.
2. Enable database page compression. This would help in reducing IO by reading more data in less IO.
3. Disable autogrow and autoshrink for the database. This means that a DBA needs to plan the database size in advance and grow it beforehand rather than letting SQL grow the file. In the same way, autoshrink is not advisable for database. This is true even for on-premises database server also.
4. Use Lock Pages in Memory. This setting would help in locking the memory and avoid paging-out the memory to pagefile.
5. C drive should not be used to store any database or SQL Errorlog or default trace file. Once SQL is installed, we might need to move all of them off the C drive to a drive which is part of Data disk.

We believe that using above guidelines, you can expect performance of a SQL Server running on Azure VM similar to an on-premises SQL Server instance.