Previously: we migrated this database from Azure SQL Managed Instance to Azure SQL Hyperscale — not because of its size at the time, but because we could see it growing, and growing faster ahead. We wanted to break the dependency between storage and compute before it started making us overpay. This part is about the compute side itself.
After the migration, the database kept growing — and over the year or two that followed it was scaled up to 32 vCores, step by step. It wasn't that we only ever threw hardware at the problem — there was optimization and refactoring along the way too — but the system kept expanding, and sometimes the simplest immediate answer was to add a little more power. That kept things running, but adding capacity is the kind of quick answer that quietly adds up on the bill — and the added power wasn't about average load anyway; it was about surviving peaks. That distinction turned out to be the whole story.
A note on how I'll talk about load
Throughout this series I use vCores as a simple, visible proxy for how hard the database is working — it's easy to reason about and easy to show. But it's only a proxy: CPU is rarely the only thing that can become a bottleneck. On Hyperscale, data IO and IOPS, transaction-log throughput, and worker or connection limits can each become the real constraint. vCores are the headline number here, not the whole picture.
The load profile
When I started, two things stood out. The steady, round-the-clock baseline wasn't small — and it was uneven, jumping up and down far more than it needed to. On top of it sat a lot of peak-generating activity: jobs every fifteen minutes or every hour, daily and monthly batch processes, synchronization with other systems, one-off daily imports, and heavy scripts from our Data Analytics Team. So there were really two problems: an uneven, higher-than-necessary baseline, and sharp spikes on top of it.
First, calming the baseline
We went after the baseline before the spikes — a jumpy baseline makes everything above it harder to read. A handful of changes brought the steady load down and made it far less uneven. The clearest example was a queue for a set of heavy calculations that ran millions of times a day, in bursts — normally ~10k calculations/min, occasionally spiking to 100–200k/min. For the subset where a short delay was acceptable, a queue pattern spread that work out over time. That evened out CPU consumption and, as a bonus, removed some of the spikes those calculations had been causing. (The queue design deserves an article of its own.) With the baseline lower and steadier, the spikes that remained were far easier to see and reason about.
Then, the spikes
With a calmer baseline, we could line up the remaining spikes against the schedules of the various jobs — biggest first, the low-hanging fruits again. Some were clearly periodic; others had no obvious rhythm and needed separate investigation.
Taking load off the primary
Quite a few clients read from this database: our own API (the main one); an external client built by a third-party company; our Data Analytics Team; and a range of our own scripts and scheduled jobs. On top of the reads, a synchronization process built with SSIS continuously loads data into this database from another of our systems, owned by a different team inside the company. Not all of that work belongs on the primary. A couple of these clients had spiky activity that interfered with the main workload — so, as a quick, cheap, and safe win, we moved them onto read-only replicas: the external client onto one, and our Data Analytics Team onto another. That external client used to connect straight to the production database; eventually it will get its data through our API instead, and then we'll be able to retire its replica altogether.
These read-only replicas run on their own separate compute, with their own CPU and memory, so read traffic on them doesn't compete with the primary — that's the whole point. One honest caveat: because a replica keeps in sync by applying the primary's transaction log, a badly undersized replica under heavy load can indirectly throttle the primary through log-rate governance. Size them sensibly and that isn't an issue; managing the replicas themselves doesn't disrupt the primary's connections either.
A few of the other fixes:
Remaining spikes traced back to application code. Analyzing what was left turned into a whole set of optimization tasks. This is a pattern worth naming: a problem shows up on the database side, but the fix often lives somewhere else — in the API or another service. The database-side fixes are usually mine; the application-side ones our Team carries through, myself included. Either way, the diagnosis usually starts at the database.
We also reviewed scheduling, so that heavy, independent jobs don't happen to fire at the same moment and create a peak.
Not only success stories
One approach I explored and rejected was auto-scaling. Azure offers it, including serverless Hyperscale — but for this workload it would have roughly doubled the compute cost. I went further and built my own experimental auto-scaler: monitor CPU, scale up when it stays high for a couple of minutes, scale down when it stays low. This is known, documented behavior: scaling provisioned Hyperscale compute up or down ends with a brief switchover that drops active connections — which is exactly why Azure tells you to build retry logic around it. For a well-behaved app with solid retries that's a non-event. But our setup is simply a different one — some clients connect directly, without reliable retry, and there are long-running scripts that a dropped connection would kill outright — so frequent automatic scaling would have caused real disruption. I abandoned the direction not because it surprised me, but because it doesn't match the shape of this system.
The result — so far
We took the production database from 32 vCores to 16 — and not in one move; we stepped down gradually, validating stability at each stage — roughly halving the compute cost of this component. I want to stress so far: this is intermediate. Steady load, without spikes, is only about 20–30%, on the order of four vCores. Sixteen is a buffer for peaks — which are much less frequent than the steady load, but far from rare — not a level we need around the clock. Just as we didn't drop to 16 in one move, we'll keep stepping down gradually — 14, 12, 10, and so on — validating stability at each step. The steady load leaves clearly meaningful room to keep going; we take it one careful step at a time, because stability comes before squeezing out the last of the savings.
Next: the database's size, and why indexes were both the problem and a big part of the solution.
Originally published as a LinkedIn post series, "The Database Marathon."