Performance pillar in the Well-Architected Framework for Azure is focused on ensuring that your workload can meet performance requirements and that it can efficiently use resources to deliver results.
There are several best practices within this pillar that can help you achieve these goals, including:
In this blog post, let us see how we can implement some of these best practices for Azure SQL Managed Instance.
Establishing Performance baselines
A baseline is a known value against which later measurements and performance can be compared. Baseline helps us define what is a normal database performance and thus comparing against the baseline provides us with insights into any abnormalities. Ideally, one should take performance measurements at regular intervals over time, even when no problems occur, to establish a server performance baseline. Compare each new set of measurements with those taken earlier.
Baselines are very critical whenever you make any changes to your database application. Common scenarios include –
It helps compare the performance of your workload after the change with your original workload.
If we take the example of a migration from an on prem SQL Server to a managed instance, we can use the following methodology for doing a performance comparison.
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 a 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.
To minimize the 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.
In the article -Compare environment settings on SQL Server and Azure SQL that may impact performance you can find the scripts 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 performance.
Create performance baseline
Select a set of queries that are important to, and representative of your business workload. Measure and document the min/average/max duration and CPU usage for these queries, as well as performance metrics on the source server, such as average/max CPU usage, average/max disk IO latency, throughput, IOPS, average / max page life expectancy, and average max size of tempdb.
The following resources can help define a performance baseline:
After you have defined a baseline, compare similar workload performance on the target SQL Managed Instance. For accuracy, it is important that the SQL Managed Instance environment is comparable to the SQL Server environment as much as possible.
There are SQL Managed Instance infrastructure differences that make matching performance exactly unlikely. Some queries may run faster than expected, while others may be slower. The goal of this comparison is to verify that workload performance in the managed instance matches the performance on SQL Server (on average) and to identify any critical queries with performance that don't match your original performance.
Performance comparison is likely to result in the following outcomes:
SQL Managed Instance has a built-in automatic plan correction feature that is enabled by default. This feature ensures that queries that worked fine in the past do not degrade in the future. If this feature is not enabled, run the workload so SQL Managed Instance can learn the performance baseline. Then, enable the feature and run the workload again with the new settings.
Automatic plan correction identifies execution plan choice regression and automatically fixes the issue by forcing the last known good plan.
Make changes in the parameters of your test or upgrade to higher service tiers to reach the optimal configuration for the workload performance that fits your needs.
If you're experiencing performance differences between Azure SQL Managed Instance and SQL server, see these tips and diagnostic checks that you can perform to identify the root causes of the performance issue....
Azure SQL Managed Instance is based on a virtual core (vCore) model, where a vCore represents a logical CPU, powered by Intel® Xeon® Scalable processors. Your costs depend on the choice and usage of the service tier you require, your hardware configuration, compute resources (the number of vCores and the amount of memory), reserved database storage, and backup storage. You control how you choose the hardware configuration so you can match compute and memory to your workload requirements. Hardware configuration options include standard-series (Gen5), premium-series, and premium-series memory-optimized. Hardware configuration defines the compute and memory limits and other characteristics that impact workload performance.
For more information on hardware configuration specifics and limitations – including which Intel® Xeon® Scalable processor is included in each series, see hardware configuration characteristics.
Selecting the right managed instance target and SKU for your database can be complicated. To make this process easier, we have the following two tools:
Both these tools collect performance data from your source SQL Server instances hosting your databases and recommend minimum Azure SQL Database, Azure SQL Managed Instance, or SQL Server on Azure VM SKU based on the data collected. The feature provides recommendations related to pricing tier, compute level, and data size.
Optimizing storage performance
Managed Instance General Purpose uses Azure Premium Storage to store database files for all databases, except for the tempdb database. From the perspective of the database engine, this storage type is remote, i.e. it is accessed over the network, using Azure network infrastructure. To use Azure Premium Storage, SQL MI General Purpose service tier takes advantage of SQL Server native capability to use database files directly in Azure Blob Storage . This means that there is not a disk or a network share that hosts database files; instead, file path is an HTTPS URL, and each database file is a page blob in Azure Blob Storage.
For the General Purpose service tier the size of a database file determines the maximum IOPS and throughput that is achievable against the file, as shown in the following table
When the IOPS or throughput generated by the database workload exceeds the limits of a database file/blob, storage throttling takes place. For MI GP instances, a typical symptom of storage throttling is high IO latency.
If you observe the symptoms of storage throttling when running workloads on MI GP(General Purpose), we recommend increasing database file size to get more IOPS/throughput from Azure Premium Storage.
Managed Instance instances do not use remote Azure Premium Storage, but instead use local SSD storage. Storage performance considerations discussed above do not apply to BC instances.
Storage performance best practices for MI GP
If sufficient storage performance cannot be achieved on a General-Purpose instance even after following the best practices described above, then switching to a Business-Critical instance is recommended.
For production workloads running on your Azure SQL Managed Instance, it is very important to continuously understand resource consumption and any potential performance issues such that you can react in time and have your business running smoothly. There exist several options for monitoring and alerting of managed instances that can be considered, be it Microsoft cloud native, on-premises products, custom solutions, or third-party solutions.
In understanding and evaluating these options there are several things you need to take into consideration depending on our workload and how critical to you are the apps you are running with managed instance. Some of the considerations you need to evaluate while deciding on a monitoring and alerting system for your managed instance are:
The options, available today to monitor Azure SQL Managed Instance resources and its performance taking the above stated objectives into consideration are:
For additional details, please refer to the below link – http://aka.ms/mi-monitoring
Improving performance over time
This involves continuously monitoring and analyzing performance data to identify opportunities for optimization and implementing changes to improve workload performance.
In traditional on-premises SQL Server, the process of initial capacity planning often is separated from the process of running an application in production. Hardware and product licenses are purchased first, and performance tuning is done afterward. When you use Azure SQL MI, it's a good idea to interweave the process of running an application and tuning it. With the model of paying for capacity on demand, you can tune your application to use the minimum resources needed now, instead of over-provisioning on hardware based on guesses of future growth plans for an application, which often are incorrect.
The following checklist can help in optimizing the managed instance and improving the workload performance.
By following these best practices, you can ensure that your Azure SQL MI workload is optimized for performance and can efficiently use resources to deliver results.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.