Set Yourself Up for Performance Success on Azure SQL Managed Instance
When you deploy SQL Server on-premises, outstanding performance is predicated on building out the most robust hardware and connectivity you can afford. When you migrate to managed database services in the cloud, you don’t want to lose performance in the process. This blog is part one of a two part series on optimizing performance for your Azure SQL Managed Instance. To get started, we recommend establishing an initial baseline, which is often based upon the performance of your on-premises servers. It is also important to define your business requirements for uptime and data redundancy, which impacts the choices you will make as you transition to Azure SQL Managed Instance. When you migrate to the cloud, you don’t want to lose performance, and armed with this information, you can match (or even improve) performance in the cloud.
Once you have your workload baseline, you will choose the appropriate service tier and the appropriate hardware configuration to meet the business needs of your workload.
Choose your service tier
Service tiers generally determine the storage type and performance, the options available for high availability and disaster recovery, as well as the availability of certain features like in-memory OLTP. SQL Managed Instance offers two service tiers: the General Purpose service tier and the Business Critical service tier.
The General Purpose tier is the default tier in SQL Managed Instance and designed for most business workloads. It is budget-oriented and scalable both for compute and storage. It provides one replica. With a geo-replica, you can provision a secondary instance to be read-only. The General Purpose tier architecture relies on two layers – a stateless compute layer operated by Azure Service Fabric and a stateful data layer in Azure Blob storage.
Get the best performance from the General Purpose service tier by increasing your data file size. In General Purpose, every database file gets dedicated IOPS and throughput that depend on file size. Larger files get more IOPS and therefore, more throughput. If you see high IO latency on some database file or note that IOPS/throughput is nearing the limit, try increasing the file size.
The Business Critical service tier is based on a cluster of database engine processes to increase availability, resilience, and I/O performance. Business Critical provides four high-availability replicas - one primary replica and three secondary replicas. Only one of the three secondary replicas is available for read-only queries. Compute and storage deployed on the Business Critical tier are integrated on each node, using locally attached SSD for data storage for low latency. The Business Critical service tier is designed for databases that require extremely low latency (1-2 milliseconds), higher availability, advanced data corruption protection, fast geo-recovery, or if you need to route reporting and analytic queries off the workload service to a read-only replica.
Select your hardware configuration
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. .
With your instance setup, we can explore a couple of other areas where we can boost performance further.
Address contention issues with TempDB configurations
TempDB is crucial to the performance of your instance because the workload in TempDB differs from the workloads in other user databases such that objects and data are frequently created and destroyed and there is extremely high concurrency. You have one TempDB on each server and all databases and applications connected to your server share that TempDB. Heavy TempDB usage may snarl a service as it contends with trying to allocate pages. Depending on the degree of contention, this may cause queries and requests that involve TempDB to lag, sometimes significantly. For this reason, optimizing your TempDB is critical to high performance.
Increasing the number of data files in your TempDB can improve concurrency of your instance’s TempDB and maximize disk bandwidth as it effectively creates one or more Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM) pages for each data file. Resource contention of the PFS page is reduced because eight pages at a time are marked as FULL because GAM is allocating the pages. Note, though, that increasing the number of files is not always a good solution. Increased TempDB data files may hurt certain workloads. For example, in SQL Server versions (2016 and later), when SQL Server auto-grows a TempDB data file, all the files auto-grow at the same time and every increase has a performance cost. Therefore, the more data files TempDB has, the bigger effect it has on performance. Startup of the instance lasts longer, as well, due to zeroing of more files. There is no one-size-fits-all number of TempDB files. The elusive right number depends on the contention seen in TempDB. That will be unique to each of your workloads and can be managed via a cautious trial.
While you can configure the number of TempDB files in Azure SQL Managed Instance, the default configuration is 12 TempDB data files and 1 TempDB log file. You can have up to 128 TempDB files. If you opt to change that number, you can do it in the same manner as on SQL Server on-premises; by adding or removing a TempDB data file(s). Note that you do not have to restart the server after adding new files; however, the emptier files will be filled with higher priority and the round-robin algorithm for allocating pages will not work properly for a bit until the system rebalances. You can also configure the maximum size of TempDB to better support your workload, and we recommend applying the same size across your TempDB data files to provide a higher degree of efficiency across the operations that use TempDB.
Optimize performance with in-memory technologies
Achieve performance improvements in online transactional processing (OLTP), online analytical processing (OLAP), and mixed (hybrid transaction/analytical processing (HTAP)) workloads with in-memory technologies.
In-memory OLTP increases the number of transactions per second and reduces latency for transaction processing. High-throughput transaction processing such as trading and gaming, data ingestion from events or IoT devices, caching, data load, and temporary table and table variable scenarios, all benefit from in-memory OLTP. Note that in-memory OLTP is only available on the Business Critical service tier.
Other in-memory technologies include clustered columnstore indexes, non-clustered columnstore indexes for HTAP, and memory-optimized clustered columnstore indexes for HTAP. Clustered columnstore indexes reduce your storage footprint (up to 10 times) and improve performance for reporting and analytics queries. You can use it with fact tables in your data marts to fit more data in your database and improve performance. You can also use it with historical data in your operational database to archive and query up to 10 times more data. Non-clustered columnstore indexes let you bypass the expensive extract, transform, and load (ETL) process and wait associated with a data warehouse to populate. You can query directly. Non-clustered columnstore indexes allow fast execution of analytics queries on the OLTP database, while reducing the impact on the operational workload. Memory-optimized clustered columnstore indexes enable you to perform fast transaction processing and run analytics queries on the same data at the same time.
Laying the groundwork for Azure SQL Managed Instance performance is only the start of the story. Once your instance is up and running, you will need to monitor and optimize performance, so stay tuned for part two of this blog. In the meantime, read this benchmark study to understand the price-performance benefits of SQL Managed Instance.
For more information, join us for our Set Yourself Up for Performance Success on Azure SQL Managed Instance webinar.
Microsoft
