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

[JovanPop]

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. 

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

1. Compare the environment settings on SQL Server and Managed Instance such as cores, memory, compatibility levels, etc. 
2. Create performance baseline on source SQL Server with average CPU/usage, perf of critical queries.
3. Compare performance on Managed Instance with the baseline

In the following sections will be described the best practices 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 high-availability capabilities. This means that you might experiences 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.

Two very common reasons that cause performance differences are:

• You are comparing two instances with the different technical characteristics (cores, memory)
• There are some instance/database settings that are causing differences (compatibility levels, legacy cardinality estimator, maxdop, cost threshold for parallelism)

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:

• Monitor CPU usage on your SQL Server instance and check how much compute power (CPU cores) you currently use (using Dynamic Management Views, SQL Server Management Studio, or other monitoring tools). You can provision a Managed Instance that matches the number of cores that you are using on SQL Server, keeping in mind that CPU characteristics might need to be scaled to match the characteristics of the VM on which the Managed Instance is installed.
• Check the amount of available memory on your SQL Server instance and choose the service tier that has a matching amount of memory. Measure page-life expectancy on your SQL Server instance to determine do you need additional memory.
• Measure IO latency of the file subsystem to choose between General Purpose (5-10ms) and Business Critical (1-3ms) service tiers.

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 scripts that you can use to compare environment settings. Scripts used in this article are found on SQL Server GitHub repository:

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

Try to resolve the differences or understand how the differences 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:

• Monitor CPU usage on your SQL Server instance and record the average and peak CPU usage.
• Monitor memory usage on your SQL Server instance and determine the amount of memory used by different components such as buffer pool, plan cache, column-store pool, In-memory OLTP, etc. In addition, find average and peak values of Page Life Expectancy memory performance counter.
• Monitor disk IO usage on the source SQL Server instance using dm_io_virtual_file_stats view or performance counters. Try to measure IO performance on databases where you are running the workload and tempdb. See this article from more details.
• Monitor workload and query performance for your SQL Server instance by examining Dynamic Management Views or Query Store if you are migrating from SQL Server 2016+ version. Identify average duration and CPU usage of the most important queries in your workload to compare them with the queries that are running on the Managed Instance.

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 known 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 are sure that Managed Instance and SQL server do not have critical differences and when you have information about the performance baseline on SQL Server, 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 enable Query Store and Automatic tuning on your Managed Instance. These features enable you to measure workload performance and automatically fix potential performance issues. Learn how to 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.

Resolve performance issues

If you see performance differences in queries that are not running fine on Managed Instance, you should identify the root causes and resolve the issues.

• Performance of the workload on Managed Instance is lower. In this case you should make some configuration change on your managed instance to configure it to fit your workload.
• Workload performance is similar with some exceptions where some of the queries might run slower. In this case, you should focus on the queries with different performance and try to fix them.

These two cases will be discussed in more details in the next two sections.

Case 1: Performance of most of the queries are different

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:

1. In most 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.

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 the Database Engine to produce the same good plan as on your older version of SQL Server.
2. If the SQL plans are same, check if the query plan uses more resources on Managed Instance.

• In other cases, you will notice you are hitting limits on data or log files and you might need to pre-allocate the files.

As a results of this comparison, you can 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 best practices during migration on Azure SQL Managed Instance documentation