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

GDeveloper92 1 Reputation point

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.

Azure SQL Database
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,461 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Oury Ba-MSFT 14,061 Reputation points Microsoft Employee

    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.


    1 person found this answer helpful.

  2. Alberto Morillo 32,006 Reputation points MVP

    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.

    0 comments No comments

  3. Erland Sommarskog 93,056 Reputation points

    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.