Poor Performance from Specific VMs to Azure SQL Database

Question

Hi All,

I've got 3x VMs in Azure - all in UK South, and 1x Azure SQL Database (Business Critical), also in UK South. 1x of the VMs is a development system, and the other 2x are the production web pool. The dev VM is a B2s and the production systems are higher spec B2ms. All are Windows Servers.

If I run our application on our dev system, it loads queries/pages quickly - almost instantly. If I do the same on either of the production systems, they take around 3-4 seconds, or roughly twice as long to execute. Identical setup to the dev system.

The code and connection string to the SQL database is identical on the production as it is on the dev system.

The only difference is the 2x production VMs are in different zones (one is in Zone 1, the other is in Zone 2). The dev system is in Zone 1.

Any ideas what to try next to improve this?

Answer 1

Hello Richard Parry - Thanks for reaching Microsoft QnA platform

It seems you arre experiencing performance issues with your production VMs that aren't present in your development environment when connecting to your Azure SQL Database.

Here are a few things you can check and try out to help get those queries executed faster:

Run Azure Performance Diagnostics (PerfInsights): Start by collecting a baseline of the VM performance using Azure Performance Diagnostics (PerfInsights). This tool can help identify any resource contention that may be affecting performance.

Please go to the Azure portal and check the Metrics section for your VMs. Look specifically at:

• VM Cached Bandwidth and IOPS percentage
  • VM Uncached Bandwidth and IOPS percentage
  • Data Disk IOPS and Bandwidth percentage
  • OS Disk IOPS and Bandwidth percentage
  Metrics will help you determine if any of these resources are being maxed out when the application is running.

1. Also ensure you're following best practices for SQL Server on Azure VMs. Common issues like incorrect storage types or VM sizing can lead to performance degradation.
2. If you're not already using it, consider upgrading to Premium Storage for your Azure SQL Database, as this can provide much lower latency and higher performance for your workloads.

Investigate Network Latency: Since one VM is in Zone 1 and the other in Zone 2, there may be network latency issues. Ensure that all VMs are connected in the same zone, or test network latency specifically from your VMs to the Azure SQL database.

Also, check if you are using the temporary disk for TempDB. Configuring TempDB on a temporary disk can improve performance as per Microsoft guidelines.

1. Upgrade VM Size: If diagnostics indicate that your VMs are hitting resource limits (CPU, Memory, etc.), consider resizing them to a larger SKU. Check the performance best practices for VM sizes to choose an appropriate size.
2. B Series CPU Credit Model - https://learn.microsoft.com/en-us/azure/virtual-machines/sizes/b-series-cpu-credit-model

By reviewing these factors, you'll be able to identify the causes of slower performance on the production VMs and determine the most effective ways to address them.

Regards

Himanshu Shekhar