Azure SQL Managed Instance Tsql queries running much slower outside of ssms than on premise

Question

After transferring on-premise databases into Azure managed instance the TSQL queries are running much slower from within visual studio application than before. Also scaled up the resources to see if that changes the speed however it was still far slower than on premise.

Answer 1

Hi @GDeveloper92 Thank you for posting your query on Microsoft Q&A.

My understanding is that you are seeing slow running queries after transferring on-premises databases into Azure managed instance. Please let me know if that is not the case.
In addition to the above answers. You will need to check what limits (CPU, memory, IOPS, etc) are being hit. The portal and/or sys.dm_db_resource_stats have lots of detail on that; if they were running query store on-prem, then comparing plans has lots of info on waits before and after. what's changed - compare the on-prem configuration with the SQL MI configuration. The transaction log throughput limit can be a key difference, since that's not present on-prem.

Regards,
Oury

Answer 2

Please defragment all your indexes and update statistics with full scans after migrating the data. Use Ola Hallengren scripts to achieve this.

Allow me to recommend you these best practices for the storage subsystem.

This article may help you troubleshoot performance issue.

Answer 3

So the queries run faster from SSMS than the application? I have an article Slow in the application - Fast in SSMS? where I explain why you could see the difference between SSMS and the application. The most common reason is parameter sniffing, but there are a few more. Including the use of MARS.