SQL Hyperscale Tier Performance Update

Based off my video at https://youtu.be/dDxunrRoJ7Y using its transcript and fed into Gpt-4o hosted in Azure AI Foundry with some human editing after.

In this article, I want to quickly go over some of the recent changes to performance for the Azure SQL Hyperscale tier.

Capacity and Transaction Logging

The first change is enhanced capacity. The maximum size of a single database (not an elastic pool, but a single database) has been increased from 100 terabytes to 128 terabytes. If you’re wondering why 128 terabytes—since it seems like an unusual number—it’s because this aligns the maximum capacity with that of many other competitive products. This increase represents just over a 25 percent boost in maximum capacity.

The second major update is related to transaction logging and how quickly data can be inserted into the database. Previously, the maximum throughput was 100 megabytes per second. Microsoft has increased this by 50 percent, bringing the maximum to 150 megabytes per second. This means you can now perform 50 percent more inserts into your database, and this improvement applies to both single databases and elastic pools.

Continuous Priming

However, the key feature I want to focus on in this article is something called continuous priming. This feature is currently in limited public preview and can be requested via a form. The concept behind continuous priming revolves around improving failover performance by reducing the time it takes for high availability (HA) replicas to become fully operational.

Let’s break it down. In a Hyperscale environment, you have a primary replica, which is responsible for handling the main workload. Behind the scenes, there are multiple page servers that store the database’s content. The primary replica interacts with these page servers and maintains a hot cache—a local cache of frequently accessed pages. This hot cache ensures faster interactions with commonly used records.

Now, in addition to the primary replica, you can have zero to four HA replicas. These replicas also have a cache, but initially, their cache is empty. If a failover occurs—where one of the HA replicas takes over as the primary replica—there’s a performance impact because the new primary replica’s cache needs to be populated with data from the page servers. Microsoft’s testing, using their OLTP benchmarks, showed that this cache population process could take about four minutes, leading to a temporary performance penalty.

This is where continuous priming comes in. Continuous priming introduces a new process across all compute layers for both the primary and HA replicas. Here’s how it works:

1. The primary replica tracks its hot pages—those pages it frequently accesses.
2. At very short intervals, the primary replica informs the page servers of its active page list, essentially saying, “These are the pages I’m actively using.”. The page servers then maintain this active page list.
3. The HA replicas, also at short intervals, query the page servers to retrieve the hot pages.
4. The page servers respond by sending the actual hot pages to the HA replicas, which then populate their caches.

As a result, the HA replicas maintain a populated cache that is ready for failover. In Microsoft’s testing, this reduced the failover cache population time from four minutes to under one minute. While there’s still a small performance penalty due to the cadence of updates (since this process isn’t real-time), the impact is significantly reduced.

Importantly, there’s minimal overhead associated with this feature, even if you have all four HA replicas enabled. Continuous priming is available for both single databases and elastic pool configurations, making it a highly beneficial capability.

Summary

1. Capacity: Increased to 128 terabytes.

2. Log Performance: Improved by 50 percent, now supporting up to 150 megabytes per second.

3. Continuous Priming: A game-changing feature that minimizes performance penalties during failovers by keeping HA replica caches populated.

I hope this update helps! Until the next article, take care. 🤙