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:
In the following sections will be described the best practices and the recommended approaches
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:
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 scripts that you can use to compare environment settings. Scripts used in this article are found on SQL Server GitHub repository:
Try to resolve the differences or understand how the differences would affect the performance.
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 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.
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.
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:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.