Azure SQL MI degraded performance compared to On Prem

Question

Hi

We recently migrated our On Prem SQL Server databases to Azure SQL MI using azure data migration service in Azure Data Studio

We see a degradation in performance.

Queries are taking way more time

SQL On prem - 3s - SSMS/ADS

SQL Mi - 1m 22s - SSMS/ADS

All the queries are performing slow since migration

We tried to check if its regional/connectivity issue but we have fabric which is in same region and though that also it is taking a 1m 17s

We checked the statistics,query plans,CPU , Space,Db size all looks fine through DBWatcher

Any help to troubleshoot is appreciated

Answer 1

Hi@ Nitya V,

Thanks for the question and using MS Q&A platform.

It seems that your Azure SQL Managed Instance is performing slower after the migration. Here are some reasons why its performance may have degraded compared to your on-premises setup.

• Azure SQL Database uses the full recovery model, which logs more data. This can slow down data modifications (DML operations). Ensure both systems use the same recovery model for a fair comparison.
• Azure SQL Database manages resources to maintain high availability, which may limit performance during heavy operations like bulk loads or index rebuilds. Compare similar high-availability setups, like Always On availability groups, between both environments.
• Automated backups in Azure can affect performance because they consume resources. Make sure backup schedules are aligned between environments to avoid skewed results.
• The speed and reliability of the network can impact performance. Ensure that your applications and databases are in similar network setups.
• Azure SQL Database uses Transparent Data Encryption by default, which can increase CPU usage and latency. Make sure encryption settings are the same in both environments.
• Ensure that compatibility levels, configurations, and any trace flags are aligned for accurate comparisons
• Differences in CPU cores, speed, memory, and input/output characteristics can lead to performance issues. Consider the specific Azure tier used and its hardware capabilities.

Troubleshooting steps:

• Ensure both databases use the full recovery model.
• Adjust SQL Server settings to align with Azure’s resource limits.
• Check wait statistics for bottlenecks.
• Schedule backups during off-peak times or disable during tests.
• Turn off Transparent Data Encryption for testing if allowed.
• Verify if TempDB encryption affects performance.
• Upgrade cores or memory as needed.
• Adjust initial file sizes based on workload.
• Ensure log files are adequately sized.
• Consider Azure Premium for better performance.
• Use Azure insights to find and improve slow queries.
• Regularly refresh statistics and adjust indexes.

For more Information, please refer to this Document: Key causes of performance differences between SQL managed instance and SQL Server

Hope this helps. Do let us know if you any further queries.If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.