Maximize performance and scale with new SQL Managed Instance capabilities - Part 3

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 see Getting 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 learn about how you can upgrade your infrastructure with the latest virtual machines for general-purpose and memory-intensive workloads, see Upgrade your infrastructure with the latest Azure VMs. 

 

Superior performance benchmark test results

 

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 3^rd 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:

• Instance #1: Standard-series hardware;
• Instance #2: Premium-series hardware, and
• Instance #3: Memory optimized premium-series hardware

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:

 

 

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:

 

 

 

Learn more

• View an on demand webinar, “Modernize critical workloads on Azure SQL Managed Instance” 
• Azure SQL Managed Instance Business Critical 16 TB is now in limited public preview - Microsoft Tech Community
• Announcing the new premium-series hardware for SQL Managed Instance - Microsoft Tech Community
• SQL Managed Instance resource limits documentation page
• Learn more about PaaS vs IaaS in our Azure SQL for beginners video series

 

Try it out

• Get an Azure account
• Get started with SQL Managed Instance  
• Find How-to topics for SQL Managed Instance 
• Explore the comprehensive SQL Server Managed Instance documentation 

 

Future topics in this series

Stay tuned for more blogs in the Azure SQL Managed Instance: Technical blog series:

 

• Business continuity: Use mechanisms, policies, and procedures that enable your business to continue operating in the face of disruption. 
• Securing your data on SQL Managed Instance: Build and maintain a strong security posture. 
• Migration tools: Learn about free migration tools and programs to help you on your journey to the cloud.

 

Past topics in this series

In case you missed it, catch up on the previous blogs in the Azure SQL Managed Instance: Technical blog series:

 

• Modernization vs migration: Determine which is right for you, including how to choose between IaaS and PaaS.
• Application compatibility: Discover the most friction-free path to the cloud through assessments and planning