The best practices for performance comparison between Azure SQL Managed Instance and SQL Server

One of the most common tasks that you are performing while migrating from SQL Server to Managed Instance is a comparison of workload performance between the source and destination instance. 

In this post you will see some recommended tools and that you should apply while doing performance comparison. The recommended performance comparison process has three stages:

  1. Compare the environment settings on and Managed Instance. 
  2. Create performance baseline on source SQL Server
  3. Compare performance on Managed Instance with the baseline

In the following sections will be described the and the recommended approaches 

Compare environment settings

The first thing that you need to do is to ensure that the characteristics of your source SQL Server are comparable with the destination Managed Instance. Managed Instance is the latest version of SQL Server Database Engine hosted in special environment in the Azure cloud with built-in capabilities. This means that you might expect differences in the workload performance compared to your on-premises version of SQL Server. Learn about the key environment differences that might cause the performance differences between Managed Instance and SQL Server and identify the risks that might affect the performance.

In order to minimize number of factors that could affect performance of your workload, you should try to align the settings on your Managed Instance with the original settings on the source SQL Server.

First make sure that you choose the Managed Instance configuration with the characteristics that match your SQL Server database. The most important factors for comparison are number of cores, amount of memory, and IO characteristics. Use the following methods to verify that you have configured the Managed Instance with the matching characteristics:

Also, make sure that you implement storage best practice guidelines for General Purpose such as pre-allocating the size of the files to get the better performance. Beware that increasing data files or log file sizes might improve performance of your General Purpose instance. You should perform these settings before you start comparison.

There are also many settings like compatibility levels or cardinality estimation algorithms, that can cause unexpected difference in performance. In this article you can find the that you can use to compare environment settings. used in this article are placed on SQL Server GitHub account:

  1. Get-properties.sql that reads various instance, database, tempdb, trace flag values and produces one XML document with all properties. You should execute this script one on the source database and then on the destination database.
  2. Compare-properties.sql that compares the values in XML generated by the previous script and shows the differences.

Try to resolve as much as possible differences or understand how the difference would affect the performance.

Create performance baseline

If you need to compare the performance of your workload on Managed Instance with your original workload running on SQL Server, you would need to create a performance baseline that will be used for comparison.

Some of the parameters that you would need to measure on your SQL Server instance are:

If you notice any issue with your workload on SQL Server such as high CPU usage, constant memory pressure, tempdb or parametrization issues, you should try to resolve them on your source SQL Server instance before taking the baseline and migration. Migrating know issues to any new system might cause unexpected results and invalidate any performance comparison.

As an outcome of this activity you should have documented average and peak values for CPU, memory, and IO usage on your source system, as well as average and max duration and CPU usage of the dominant and the most critical queries in your workload. You should use these values later to compare performance of your workload on Managed Instance with the baseline performance of the workload on the source SQL Server.

If possible, take the actual execution plans of the most critical queries so you can compare them with the execution plans generated by Managed Instance.

Compare performance with the baseline

Once you have sure that Managed Instance and SQL server don't have some critical differences, and when you have information about the performance baseline on SQL Server, now it is time to run the workload on Managed Instance and measure the performance.

Make sure that you keep the same database compatibility level as on your source SQL Server database and keep enabled Query Store and on your Managed Instance. These features enable you to measure workload performance and automatically fix the potential performance issues. Learn use Query Store as an optimal tool for getting information about workload performance before and after database compatibility level change, as explained in Keep performance stability during the upgrade to newer SQL Server version.

Once you have prepared the environment that is comparable (as much as possible) with your on-premises environment, you can start running your workload and measure performance again. Make sure that you are using the same tools both on SQL Server and Managed Instance to measure performance to get the consistent results.

As a result, you should compare performance parameters with the baseline and identify the critical differences.

There are three possible outcomes of this comparison:

  1. Performance of the workload on Managed Instance are fine in most of the cases. In this scenario you have successfully completed your performance test.
  2. Performance of the workload on SQL Server is better than performances of the same workload on Managed Instance for most of the queries in your workload. Now 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 few iterations of changes you would align workload performance 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.
  3. Performance of the workload is fine, with some exceptions where performance of a couple of queries are 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:
    1. Compare the SQL plans of the queries on SQL Server with the queries on Managed Instance. If the query plans are different try to apply some query hints that change compatibility levels or cardinality estimators. You can apply these hints directly in the queries or use the plan guides to modify the SQL plan. The goal is to force Database Engine to produce the same good plan as on your older version of SQL Server.
    2. If the SQL plans are same, check does the query plan uses more resources on Managed Instance.
      1. In most of the cases you will see that you are hitting instance log rate limit and there is nothing that you can do in this case.
      2. Sometimes you will notice some unexpected action like ongoing automatic backup that can temporary affect performance – see the example in TPCC experiment.
  • In other cases, you would notice the limits that you are hitting on data or log files and you might need to pre-allocate the files.

As a results of this comparison, you would validate that the performance of your workload is matching the source SQL Server or you will identify the factors that might cause the difference and fix these issues.

Find more information about the during migration on Azure SQL Managed Instance documentation 


This article was originally published by Microsoft's Azure Blog. You can find the original article here.