One of the most common tasks that you will be performing while migrating from SQL Server to Managed Instance is a comparison of workload performance between the source and destination instance.
If the performance of the workload on SQL Server is better than performance of the same workload on Managed Instance for most of the queries in your workload, you need to change something in you database or Managed Instance. Do not try to analyze and correct individual queries, because there is probably some global setting that will fix most of them.
You would need to identify what could cause the differences. In most cases it is some of the differences that might cause the performance differences between Managed Instance and SQL Server.
Once you identify the root causes, you should try to fix them. Since most of the queries are affected in this scenario, you should probably try to upgrade to the latest compatibility level and apply some query optimization hints at the database level. If you notice that IO is the bottleneck in General Purpose instance, you might need to increase data files or log file sizes might improve performance of your General Purpose instance. After a few iterations of changes except to see the workload performance align with your SQL Server workload, unless if there are some physical limitations that affect your workload, for example instance log rate limit or not enough memory.
Case 2: Performance of some queries are different
In this case, you know that performance of the workload is fine, with some exceptions where performance of a couple of queries is degraded. In this case you should try to fix these queries because you have most of your workload tuned. Try to identify why the queries are slower before making some changes – some hints are:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.