In this mini-series, I will go through ways of troubleshooting your Azure Virtual Machines. This follows the trajectory of a project I've been working on, so I thought I'd blog about it. In the first instance, I'm looking at Disk Performance. So, where do you start?
Microsoft Azure IaaS (Windows) diagnostics package
This self-help diagnostic tool will walk you through how to troubleshoot issues on Microsoft Azure Windows-based virtual machines (VMs) by using the Guest Agent. This Microsoft Azure infrastructure as a service (IaaS) Support Diagnostics Platform (SDP) package can be configured to run a variety of operations, including resetting a local administrator account password, identify Azure VM and Azure platform storage and disk configuration issues that can cause disk performance issues, and establishing a remote PowerShell session to Azure VMs.
How do you use this tool?
To use this tool, you will enter his Azure Subscription credentials.
SDP Package will provide Fix-It scenarios
How does it work?
The tool uses VM Agent Extension and Remote Powershell in order to operate. You can read more about it in the following Microsoft blogs:
How do we troubleshoot performance?
Key Takeaway: Remember that a Virtual Machine in Azure is like any other Virtual Machine.
A VM In Azure is like an on-premise Virtual Machine
Yup, I said it again. Because it's in the 'cloud' doesn't mean it's something you can't use your existing skills. So, we bring our usual tools into play:
- Perfmon - here is a sample method for using Perfmon to measure performance
- SQL Profiler - Here is the SQL Profiler, manifested in SQL Server 2016
The best advice I can offer it to use Azure Premium Storage. This tier offers high-performance storage for Azure Virtual Machine Workloads. You can read the detail over at the Azure website.
Azure Premium Storage delivers high-performance, low-latency disk support for virtual machines running I/O-intensive workloads. They run solid state drives (SSDs) - SSD for all Disks, and it supports attaching several Premium Storage disks. This means that your applications can have up to 64 TB of storage per VM. With Premium Storage, your applications can achieve 80,000 IOPS (input/output operations per second) per VM, and 2000 MB per second disk throughput per Virtual Machine with extremely low latencies for read operations. You can read more over here.
How are you copying your files into storage?
Are you using File Copy? Well, take note of this blog by Jose Baretto over at Microsoft. You may be causing issues if you do, and here's what to do to take care of it.
Performance Best Practices for Azure Virtual Machines with SQL Server
You can get the full download paper here, but here's a handy checklist which I've taken from the paper, for your convenience. Credit to Microsoft team involved: Silvano Coriani, Jasraj Dange, Ewan Fairweather, Xin Jin, Alexei Khalyako, Sanjay Mishra, Selcin Turkarslan
• Use minimum Standard Tier A2 for SQL Server VMs.
• Keep the storage account and SQL Server VM in the same region.
• Disable Azure geo-replication on the storage account.
• Avoid using operating system or temporary disks for database storage or logging.
• Avoid using Azure data disk caching options (caching policy = None).
• Stripe multiple Azure data disks to get increased IO throughput.
• Format with documented allocation sizes.
• Separate data and log file I/O paths to obtain dedicated IOPs for data and log.
• Enable database page compression.
• Enable instant file initialization for data files.
• Limit or disable autogrow on the database.
• Disable autoshrink on the database.
• Move all databases to data disks, including system databases.
• Move SQL Server error log and trace file directories to data disks.
• Apply SQL Server performance fixes.
• Setup default locations.
• Enable locked pages.
• Backup directly to blob storage.
In the next topic, I'll look at errors that you might see in Azure Storage itself, why these might occur, and what you can do about it.