Azure SQL Managed Instance is a fully-managed, secure, and always up-to-date SQL instance in the cloud. It provides state-of-the-art capabilities and industry-leading SLAs, allowing you to easily migrate and modernize your mission-critical applications in Azure.
Azure SQL Managed Instance is constantly being improved based on the customer feedback. There are many important updates to Managed Instances that will be launched as part of Ignite 2020 virtual conference (more details to follow in the upcoming blog posts), but in this post we'll go deeper into the recent improvements in the areas of performance and cost efficiency.
Significantly improved transaction log write throughput
We have significantly improved the transaction log write throughput (often referred to as "log rate") for the Azure SQL Managed Instances, greatly improving the overall performance for usage scenarios such as data ingestion and index maintenance.
The following table and the diagram summarize the improvements:
|
|
Previous log rate limit |
New log rate limit |
|
General Purpose |
3 MB/s per vCore |
3 MB/s per vCore |
|
Business Critical |
4 MB/s per vCore |
4 MB/s per vCore |
General Purpose instances now have more than 5x larger per-instance log rate cap compared to the previous setting (120 MB/s versus 22 MB/s). However, the per-instance log rate cap cannot be achieved by a single database. The per-database log rate cap depends on the log file size (as explained in this blog post), and can go from 22 MB/s to 65 MB/s, as per the summary in the following table:
|
Log file size |
Log rate limit |
|
<= 128 GB |
22 MB/s |
|
<= 512 GB |
30 MB/s |
|
<= 1 TB |
50 MB/s |
|
<= 2 TB |
65 MB/s |
The significantly improved log rate makes the General Purpose service tier an ideal candidate for hosting smaller (<8 TB in size) data marts and warehouses, due to greatly improved data loading speed.
When it comes to Business critical instances, the log rate cap is flat out doubled from 48 MB/s to 96 MB/s, with no per-database cap limitations (i.e. a single database on a Business Critical instance can use up the whole 96 MB/s log rate quota).
Improved data and log IOPS for Business Critical instances
Business Critical instances now have significantly improved IOPS rates for data and log operations. Previous limit of 2500 IOPS per vCore is now increased to 4000 IOPS per vCore, providing a fantastic 60% increase.
Improved IOPS rate will make the Business Critical tier an even better fit for the high-volume transactional processing workloads (OLTP scenarios).
Improved performance predictability for General Purpose instances
In one of our previous blogs we’ve shared the details on how the storage subsystem of General Purpose Managed Instances is designed. Essentially, we're using remote storage (Azure Storage Premium Disks) to store the database and log files. While this design is both simple and scalable, it presents an interesting set of challenges since every disk I/O in Azure SQL Managed Instance becomes a network I/O against another Azure service.
We've taken great care to optimize the integration with Azure Storage to maximize the throughput, reduce the latency and improve the performance predictability. Our I/O scheduling, throttling and governance algorithms have been tweaked to ensure the smoothest possible experience for our customers.
Improved tempdb performance
Previously, the tempdb I/O operations were governed as part of the instance log rate cap (which used to be configured to 22 MB/s for General Purpose and 48 MB/s for Business Critical). With this set of improvements, tempdb I/O operations are no longer governed as part of the instance log rate cap, allowing for a significantly higher tempdb I/O rates.
The improved tempdb performance will greatly improve the speed of tempdb-bound operations, such as running queries with large sorts/spills, or data loading through tempdb.
Testing Results
When all the above improvements are combined, the end-result is a significant improvement to the performance of your SQL Managed Instance. The exact performance gain will vary based on your chosen service tier and your database workloads, but the improvements we've seen based on our testing are very encouraging:
- TPC-C – up to 2x-3x transaction throughput
- TPC-H – up to 23% lower test execution time
- Scans – up to 2x throughput
- Data Ingestion – 2x-3x data ingestion rate
More bang for your buck
These improvements are a fantastic value for our customers since there are no associated pricing changes. Basically, we’ve upgraded the “horsepower” of the SQL MI engine for no extra charge, providing an even better value for your investment.
Also, these changes were automatically enabled for all existing and future Azure SQL Managed Instances during the first half of September - we expect our customers will be pleasantly surprised when they discover this.
Conclusion
These performance improvements make Azure SQL Managed Instance an excellent choice for your performance-hungry database workloads.
And if you're still new to Azure SQL Managed Instance, now is a great time to get started and take Azure SQL Managed Instance for a spin!
References
- For more details on Azure SQL Managed Instance resource limits, check out the public documentation
- For details on other improvements to Azure SQL Managed Instance launched at Ignite 2020, please see this blog
Microsoft
