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 best practices that you should apply while doing performance comparison. The recommended performance comparison process has three stages:
- Compare the environment settings on SQL server and Managed Instance.
- Create performance baseline on SQL server
- 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 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:
- 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, having in mind that CPU characteristics might need to be scaled to match VM characteristics where Managed Instance is installed.
- Check the amount of available memory on your SQL Server instance, and choose the service tier that has matching amount of memory. It would be useful to 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 placed on SQL Server GitHub account:
- 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.
- 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:
- 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, you should 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 or 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 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 SQL Server database and keep enabled Query Store and Automatic tuning on your Managed Instance. These features enable you to measure workload performance and automatically fix the 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.
There are three possible outcomes of this comparison:
- Performance of the workload on Managed Instance are fine in most of the cases. In this scenario you have successfully completed your performance test.
- 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.
- 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:
- 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.
- If the SQL plans are same, check does the query plan uses more resources on Managed Instance.
- 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.
- 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 best practices during migration here on Azure SQL Managed Instance documentation