In this post, I am going to talk about Performance Tuning Best Practices for SQL running on an Azure VM with key considerations. Understand the characteristics of running SQL Server on a Windows Azure VM (IaaS). and what impacts performance. Additionally, learn how to monitor and troubleshoot performance of SQL Server running on Windows Azure VM. Let’s start with the best Practices for SQL on Azure VM.
Note: If you buy something from our links, we might earn a commission. See our disclosure statement.
Read my earlier post on SQL Server Performance Tuning for All Editions.
This is a 127GB disk optimized for OS access patterns and Windows (or Linux) boot up time. This is not meant for either transactional or Data Warehouse (Analytical) workloads.
You can add up to 16 disks with 1 TB in size and 500 IOPS each.
If you agree to all the conditions mentioned above, you can still go ahead and put the TEMP DB system files on the non-persistent data drive for performance reasons.
The storage disks are implemented as a shared multi-tenant service with built-in triple redundancy, and with optional geo-redundancy across regions. Given below are the main characteristics of Azure Disk subsystem:
The VM disk is cached in both inside physical host machine and also to an external Azure Page Blobs. This greatly reduces disk I/O latency by reducing the number of transactions hitting against the Azure Storage subsystem.
The VM Cache is a 2-tier cache which stores recently accessed data in host RAM cache – space shared by all VMs on the machine in the form of Page Blobs. The second Tier stores less recently accessed data stored on local hard disks of the physical host machine. There is a reserved cache space for VM “OS Disk” and “Data Disks” based on the VM size.
Read Only: All requests cached for future reads. All writes persisted directly to Windows Azure Storage Blobs.
Read Write: This is a kind of a “Read Ahead” algorithm. The reads and writes are cached for future reads. Non-write-through writes persisted to local cache first. For SQL Server, writes are persisted to WA storage because it uses Write-through It provides the lowest disk latency for light workloads
None (disabled): This allows you to bypasses the cache. All the data are transferred to disk and persisted to the Windows Azure Storage. This method gives you the highest I/O rate for I/O intensive workloads. You also need to take “Transaction Cost” into consideration.
Caching by default is disabled on the Data Disks. You can enable caching of upto 4 Read only and 4 Read write disks for data drives.
For the OS drive, the default caching is “Read Write” which reduces latency as mentioned before.
For Data Disks, the default is “None (Disabled)” which gives you the highest throughput. For Read intensive workload, Enable “Read Only” caching for better performance.
As you can see, there is a significant reduction of logical and physical reads with page compression enabled, and the elapsed time. Unsurprisingly, CPU time of the query does go up with page compression as expected, because SQL Server needs to decompress the data while returning results to the query. Please note that your results will vary, depending on your workload and scenario.
Use these data compression techniques for I/O intensive workloads and then decide which tables and indexes to compress. If your CPU is already highly utilized, do not implement this or upgrade the VM to a higher service tier.This is not enabled by default in Azure VM images. Reduces IO for Creating a DB Restoring, DB Adding files to a DB, Extending file size, Autogrow, etc. Add SQL service account to Perform Volume Maintenance Tasks security policy.
This step will not help, if you have performance issues at the Transaction Log throughput level.
Spreading Data files of a single Database into multiple storage accounts is not recommended. This is because Blobs are not written at the same time and could be out of sync. Instead, spread the data files of different databases across multiple disks to achieve higher IOPS / bandwidth performance.
A storage account has a hard limit of 20000 tps (Transactions per second)
See my post on Free Tools for Monitoring SQL Server Performance
LogicalDisk(*)Avg. Disk Bytes/Read
LogicalDisk(*)Avg. Disk Bytes/Write
LogicalDisk(*)Avg. Disk Queue Length
LogicalDisk(*)Avg. Disk sec/Read
LogicalDisk(*)Avg. Disk sec/Write
LogicalDisk(*)Current Disk Queue Length
LogicalDisk(*)Disk Read Bytes/sec
LogicalDisk(*)Disk Write Bytes/sec
MemoryFree System Page Table Entries
Processor(*)% Privileged Time
Processor(*)% Processor Time
SQLServer:Locks(_Total)Average Wait Time (ms)
SystemProcessor Queue Length
Use tools like PAL from Codeplex to analyze the results.
I hope this was helpful. Please comment below to give feedback.
I hope that you like this post on Best Practices for SQL on Azure VM. Any comments or suggestions are welcome in the comment section below. Cheers!
Very nice job!
Thanks Mark, Appreciate the Feedback!
Very good post!
Comments are closed.