This is the third blog of a series focused on SQL Managed Instance, where we will dive deeply into technical topics that will help you successfully modernize your SQL Server workloads onto Azure.If you do not have an Azure account, get one now. If you want to jump right into Azure SQL Managed Instance, please seeGetting Started with Azure SQL Managed Instance.
SQL Managed instance offers new capabilities to boost performance and scale, giving you the most flexibility to migrate more of your mission-critical and memory-intensive workloads to the cloud. To maximize performance, the new premium-series and memory optimized premium-series hardware generations offer additional CPU speed and increased memory. To maximize scale, service tiers now offer up to 16 TB storage space.
Boost performance with premium-series and memory-optimized premium-series hardware
Prior to the recent release of the premium-series hardware generation, your only option in SQL Managed Instance for speed and memory was the standard-series (formerly known as Gen 5). With the introduction of the premium-series, your CPUs can run faster and show improved memory performance. The new memory-optimized hardware generation within the premium-series delivers even more memory for the most memory-intensive workloads.
Bringing the latest hardware innovations to Azure SQL Managed Instance
Azure SQL Managed Instance is the industry-first cloud database service that offers the latest-and-greatest 3rd Generation Intel® Xeon® Scalable Processor (Ice Lake) processors, providing major performance improvements over the previous CPU generations. The following chart shows the differences between the new premium-series and memory optimized hardware generations versus the traditional standard-series hardware generation. As you can see in the chart, the premium-series offers 40% more memory per vCore ratio than standard-series. Memory-optimized premium-series also pushes storage up to 16 TB. Read more about reducing your cost for memory-intensive workloads later in this blog.
To demonstrate performance of the premium-series and memory optimized premium-series in memory-intensive workloads, we performed two MS-OLAP-H tests. The MS-OLAP-H workload is derived from the TPC-H Benchmark and is not comparable to published TPC-H Benchmark results, as this implementation does not comply with all requirements of the TPC-H Benchmark. We are also working with an independent 3rd party vendor on benchmarking price-performance across multiple workloads - these will be published in the near future.
The tests used a 300 GB database with three instances:
The configuration for the instances included business critical, 8 CPU vCores, and 1 TB of reserved storage.
Test #1: Run a memory-intensive query
For the actual test, we took one memory-intensive query and modified it slightly to return the largest revenue result. We also increased the range of data from just 1 year to 20 years, which is a normal amount of data for big data warehouse workloads.
Test #1 results – Memory optimized premium-series is in the lead
Premium-series instance was 12% faster than standard-series, but did not have enough memory to fully avoid the spills to disk. Memory optimized premium-series instance was 55% faster than standard-series with no more spills.
Test #2: Run many queries in parallel
For the actual test, we ran many instances of the same query in parallel to test the workload performance. We chose a query where each instance needed a memory grant of ~3.5 GB. Our scenario was for 15 users that would execute the query in a loop with 3 repetitions. We ran the test using the ostress tool, which is available for download on the Internet.
Test #2 results – User experience wins with premium-service instances
The results showed a significant improvement in speed: Premium-series instance was 15% faster than the standard-series instance, and memory optimized premium-series instance was 42% faster than the standard-series instance.
The results also showed a huge improvement in customer experience: All 15 users in the test were able to execute their tasks uninterrupted in parallel, as expected. To compare, a customers would have experienced poor performance if there were queries waiting to execute, which can lead to app time-outs (for comparison, web pages typically time out in 30 seconds).
Up to 16 TB storage capacity supports scalability
We’ve increased overall storage capacity to 16 TB in both the service tiers to significantly impact the performance of your most demanding mission critical and memory-intensive workloads. For the general purpose service tier, this means doubling what's available in the standard-series, and for the business critical service tier, it means quadrupling it. This increase will provide more room for your larger workloads, and support growth into the future.
Here's a quick comparison chart:
Premium-series memory optimized
If you are on the standard series, your maximum storage is 16 TB for the general purpose tier and 4 TB for the business critical tier. To learn more on how to choose the appropriate tier, see the following articles for the business critical and the general purpose tiers.
Reduce your cost for memory-intensive workloads
When it comes to value for your money, both premium-series and memory optimized premium-series provide fantastic value for workloads that require large amounts of memory. As you can see in the chart below, the premium-series memory per vCore ratio is 1.4 times greater relative to standard-series. With the 15% price increase in price per vCore in premium-series over standard-series, you're getting 22% more memory per $$ spent. With the memory optimized premium-series, you're getting an even better value with the addition memory, at 68% more memory per $$ spent relative to the standard-series.
Total cost of ownership for memory-intensive workloads
Currently, the pay-as-you-go pricing option is available for the premium-series hardware generations. Reserved Instance pricing will become available when the new hardware generations are in general availability, scheduled for 2022.
You can view a summary of your computational and storage costs in the Azure portal:
View an on demand webinar, “Modernize critical workloads on Azure SQL Managed Instance”