DEV Community: Kostya Bartashevich

The Database Marathon — Postscript: Why I Wrote It

Kostya Bartashevich — Thu, 13 Aug 2026 13:20:55 +0000

The Database Marathon started on a bike ride. Funny, given the name.

A few weeks ago, out on the bike early on a Saturday morning, I was turning over the week in my head — work included. And it struck me that almost nobody sees the whole of it. Pieces show up in dailies, pieces in specifications, but the end-to-end picture is rare — apart from our Team Leader, who I've spent hours discussing the technical details with.

That matters more than it sounds. From the outside, months of work can look like a handful of unrelated tweaks — or like someone found a silver bullet. It was neither: it's a long chain of connected decisions, each making the next possible.

I thought about writing it up as internal documentation. But a wiki page gets read out of obligation, if at all. An article, though — an article someone might actually read, maybe even outside the team.

So I came home, and my family kindly let me disappear into the study for the day. (After second breakfast — the privilege of training early on a Saturday.)

Then I just talked. Two and a half hours into my phone: everything I could remember, out of order, with some numbers and names left as gaps.

Afterwards I cleaned up that transcript myself — sorting it, filling the gaps, fixing what came out scrambled — and handed it to Claude to help turn into an article. It suggested straight away that this wasn't one article. It was five.

From there we worked like an author and an editor: I decided what the series should say, and Claude pushed back on structure, checked facts, and caught the places where I'd been vague. It took three or four days, spread over a weekend and several evenings: edits, line-by-line proofreading, technical fact-checking, digging out details I hadn't remembered, and working out where to publish.

So, plainly: this series is based on my own experience, written with AI as a tool.

Which brings me to what nearly stopped me from publishing. The feed is increasingly full of text that reads well and says very little — AI slop, as it's known — and the accounts written by people who actually did the work get lost in it. That was exactly my worry: publishing something written with AI help and being taken for part of that noise.

The tool isn't what makes the difference. What matters is whether there's anything underneath it — experience, decisions, something at stake. Behind these five parts are months of real work; AI helped me write it down and keep it accurate. With it, the whole thing took a few days. Without it, it would have taken zero hours — because I would never have started.

And if AI can fill a feed with noise, I'd like to believe it can help clear it out as well.

Thanks to the colleagues and friends who read the early versions, pushed back, and pointed me to other places to publish. And thanks to everyone who read the series — I hope it was useful, or at least interesting.

Originally published as a LinkedIn post, alongside the series "The Database Marathon."

Optimizing an 18 TB Azure SQL Hyperscale Database — Part 5: Where AI Actually Helped

Kostya Bartashevich — Tue, 11 Aug 2026 13:36:50 +0000

Previously: cutting compute, optimizing indexes, and reclaiming terabytes on an 18 TB Azure SQL Hyperscale database — a lot of careful, conservative engineering. This final part is about the tool that accelerated it.

Now the AI part — and notice how far into the series it is. That's deliberate. AI here is a tool — leverage, in the mechanical sense. A lever multiplies the force you apply, but it doesn't decide where to apply it; someone still has to aim it and own the result. That's exactly the role AI plays in this story: it multiplies how much I can investigate, without taking over the engineering judgment or the responsibility for what gets done.

I use Claude Code for this, on our company's license, usually on the latest Opus model — which has been more than enough. What I built is explicitly not "agentic development" or autonomous AI. It's a sequential pipeline of specialized agents that analyze SQL Server and the .NET code, produce reports, propose hypotheses, and rate risk — but never change anything: not the database, schema, indexes, data, or application code. I call them "agents," though that's generous: each is really a specialized, single-purpose step in a fixed pipeline — no autonomy, no actions, just analysis passed down the line. The output is a single risk-rated Markdown report — something a human reads, reviews, and acts on manually.

Security by design

This is the core of it:

The AI never modifies the database — the account has no CREATE, ALTER, DROP, UPDATE, or DELETE permissions. It can't run stored procedures either (no EXECUTE permission), since those could change data or schema under the hood.
The AI has no access to business data — no Customer or IoT data, no personal information, no application records. It works only with metadata: schema, Query Store, DMVs, execution statistics, index metadata, wait statistics, storage metrics, performance counters.
Least privilege. There's a dedicated SQL account for the pipeline that simply cannot do dangerous things. Even if I explicitly told an agent "DROP this index" or "SELECT every Romeo living in Verona," it physically couldn't — the permissions aren't there.
Human-controlled enrichment. When an investigation genuinely needs a data-level fact — say, how many rows in a million are soft-deleted — I run that query manually, with my own developer account, and pass only an aggregated result back to the AI if it's needed.

The idea I'd most want a reader to take away: the pipeline is safe not because the agents are trusted, but because the architecture doesn't require trusting them. That's what "security by design" means here — safety comes from architectural constraints, not from hoping the model won't make a mistake.

The seven agents

The pipeline runs in order, and the order is the flow — each stage narrows the search for the real problem:

Observer — reads raw DMV and Query Store metrics; classifies slow queries, wait statistics, blocking, and resource pressure. It produces the Observer Report.
Code Analyst — searches the .NET API codebase for the objects and queries the Observer flagged, mapping database symptoms to their root causes in the application. Instead of "this query is expensive," you get "this query comes from this service / endpoint / job." It produces the Code Analyst Report.
DBA Agent — reviews schema and indexes and recommends whether an index should be created, rebuilt, reorganized, removed, or consolidated; drafts the DDL; flags schema-design issues. It can generate complete DDL — but nothing is ever executed automatically. These are recommendations only. It produces the DBA Report.
Query Analyst — focuses on the T-SQL and execution plans: eliminating unnecessary key lookups, addressing parameter sniffing, and cutting needless scans. It produces the Query Report.
Performance Engineer — looks at SQL Server as a system: MaxDOP, vCore count, auto-update statistics (including AUTO_UPDATE_STATISTICS_ASYNC — more on that one shortly), and other instance-level settings. It produces the Performance Report.
Risk Auditor — the one I find most interesting. It doesn't look for new optimizations; it adversarially challenges every prior recommendation and tries to find reasons not to do it. It classifies each as Safe, Caution, or Vetoed, weighing things like lock escalation, execution-plan stability, regression risk, and production safety. It produces the Risk Report.
Synthesizer — analyzes nothing on its own. It merges the reports, respects every Risk Auditor veto, removes conflicts, prioritizes, and produces a structured Markdown report that ends with a checklist for a human: review, validate assumptions, discuss with the Team if needed, test in non-production, decide, and only then apply — manually.

The real point isn't "seven AI agents." It's a process in which the AI is deliberately not trusted: recommendations get challenged rather than accepted, and one agent's entire job is to argue against what the others proposed. The pipeline ends not with "deploy changes" but with "human review and decision required." AI accelerates investigation; it does not make the decision.

This isn't a new principle, by the way — only a new source. Back in the era of printed programming cookbooks, you didn't copy a snippet straight into production without understanding it. The same was true later for answers from Google and Stack Overflow. It's just as true now for output from an AI model: you read it, you understand it, you take responsibility for it. The source changed; the discipline didn't.

A concrete example of what it surfaced

In one run, the Performance Engineer agent flagged something I'd never pinned down: every so often a query on the largest tables would be suspended mid-flight, waiting for a synchronous statistics refresh to finish before it could even compile. To put the scale in perspective: queries here normally return in milliseconds, but in the worst case one of these stalls lasted over two minutes — and these waits had been quietly piling up in huge numbers over time. The root cause was a single database option (AUTO_UPDATE_STATISTICS_ASYNC) left at its default — off. Turning it on — a low-risk, easily reversible change — made the multi-minute stalls disappear. That's exactly the pattern I want from AI here: the agent did the finding — correlating wait statistics to one small, overlooked configuration setting — while the validation and the decision were mine.

One honest observation — and not a new one; plenty of people are saying it now. But I can confirm it from direct experience: AI sped up my performance investigations so much that it actually created more work. Finding problems faster means generating more optimization tasks — for me and for our whole Team. So AI didn't reduce the backlog; it increased our capacity to find things worth doing. Anyone adopting it this way should expect that accelerating analysis produces a new stream of quality engineering work.

Where this is now, and where it's going

The pipeline is still evolving — a proof of concept at this stage, and I use it selectively. Often the full sequence is more than a question needs: for the obvious wins, a plain session in the AI console is enough. As the known issues are cleared and the refactoring lands, the plan is to run it periodically — health checks and scheduled investigations — and to compare database snapshots over time: not just checking what's wrong today, but comparing today's database with its previous states — trend analysis and early detection of regressions.

To close

A great deal has been done, and a great deal remains — this is an ongoing journey, not a finished project. Still mid-marathon, if you'll forgive this metaphor a second time. And the pacing is deliberate: we keep building headroom ahead of the growth, rather than waiting to hit the wall and fix things under pressure. This database has become a large part of my work, and I've come to treat its performance, cost, and long-term health as my own responsibility.

But none of it happens in isolation — the context that makes these decisions possible comes from our Team and our Data Analytics Team alike. I depend on both. And the exchange goes both ways — now and then the Data Analytics Team brings us a slow script or data that looks off, and we dig into it together, which is part of how I keep learning what the data really means.

Worth repeating once, because it's a fair business question: all of this ran in parallel with product work — new features, bug fixes, a roadmap that never paused. Refactoring while features are being built is harder than doing it in isolation, but we couldn't put the project's development on hold for it. So it was fitted around everyday development, not at its expense.

A closing thank-you

None of this was a solo effort — my thanks go to our Team, to the other teams across the company, and to everyone whose paths cross mine on this project. And a personal thanks to our Team Leader — for trusting me with this work and giving me the room to dig in, and for the many discussions we had along the way. Out of respect for their privacy I won't name them, but a good share of the credit is theirs.

If you've used AI for this kind of work — performance investigation, database optimization, that sort of thing — or you're thinking about it, I'd be glad to compare notes in the comments. What worked, what didn't, and what limits you set on it.

Originally published as a LinkedIn post series, "The Database Marathon."

Optimizing an 18 TB Azure SQL Hyperscale Database — Part 4: Reclaiming Terabytes

Kostya Bartashevich — Thu, 06 Aug 2026 13:14:22 +0000

Previously: index work freed up several terabytes and cut write overhead. But freeing space inside the database is one thing; giving it back — and cutting the bill — is another.

Size matters here for a simple reason: as the business grows, so does the data, and so does the cost — not only for the database, but for backups and long-term retention. Those bills climb month over month, and we expect the growth to be non-linear rather than flat. So once the sharpest performance problems were under control, controlling size became the next front.

Used vs. allocated space

After the optimizations, the used space dropped, but the allocated space did not follow automatically. Allocated space grows as needed but never shrinks back on its own after deletes — the engine keeps that formatted space around so future inserts stay fast. On production, used space is down from its 18 TB peak to about 11 TB today. In reality we've freed even more than that number suggests: the migration and refactoring that are still in progress temporarily occupy extra space, which inflates the current figure. Once that work completes, used space will fall further — and by a lot: the refactoring lets us drop not just the huge indexes on some tables but several large tables entirely, indexes and all, so the final size should end up far smaller than today's. Reducing used space already helps — it slows the growth of cost — but the full financial win only comes when you actually reduce allocated space. And what a shrink can reclaim is precisely that gap — the allocated-but-unused space.

Why the order mattered

Freeing this space first wasn't optional — it was what made the rest work cleanly. Before the refactoring, allocated space sat just above used space — a small buffer on top. As we kept writing more data, used space grew and slowly pushed allocated up with it: a slow, steady rise. A big online index rebuild, though, behaves very differently: it builds a fresh copy of the index alongside the old one, so it needs several terabytes of temporary room all at once, roughly the size of the index being rebuilt. With almost no free room inside the database, that would have forced allocated space to jump up by the size of the index — pushing the number up just when the whole point was to bring it down. By reducing used space first, we created that headroom inside the database, so the rebuild reused space we already had instead of spiking allocated upward. And rebuilding those indexes (as described in Part 3) before the shrink paid off a second time. A freshly rebuilt index is tightly packed, and a shrink has to move far fewer pages that way — so doing the rebuilds first, the largest indexes included, made the shrink itself faster and able to reclaim more.

A cloud database won't do this for you

You might expect a managed cloud database to reclaim unused space automatically — but it won't hand allocated space back on its own (and automatic shrink is discouraged anyway). Reclaiming it is a manual operation using DBCC SHRINKFILE. And it isn't a routine maintenance task: Microsoft is explicit that shrink shouldn't be a regular operation — it's a one-off in response to a major drop in used space, which is exactly our case here, after the index cleanup. There's also a genuinely Hyperscale-specific catch: shrink only became generally available for Hyperscale on 29 January 2025, so it's a relatively new capability there. On a database this size it's a slow, careful, iterative process that runs for weeks, and it lines up with Microsoft's own guidance — run it in off-hours or a maintenance window, use WAIT_AT_LOW_PRIORITY to limit blocking, and so on.

Our conservative approach

The "shrink script" I developed is really orchestration around DBCC SHRINKFILE. It starts with the cheap win — a TRUNCATEONLY pass, which instantly releases any allocated-but-unused space sitting at the end of a file, with no data movement. Only then does it move into the slower, data-moving shrink, done in small chunks rather than one big pass — because if a live workload grabs the just-freed space before the file is truncated, the shrink can't reclaim it, so smaller chunks make each reduction more likely to stick. Shrink does increase fragmentation, but we tolerate that: it's cleaned up afterwards by our regular index maintenance — automatic for the smaller indexes, manual for the few largest. Files are handled separately, and the process can pause and resume — including backing off on its own when the database is busy with real work, so the shrink never competes with the live workload. The goal is to bring allocated space close to used space while leaving a sensible buffer for growth — enough that the database doesn't just auto-grow straight back the moment the shrink finishes. It will grow into that buffer over time, of course; the point is not to shrink so tight that it springs right back. This orchestration is deliberately — almost obsessively — conservative; it has to be, to run safely against a heavily loaded production database, and it's based on a lot of the lessons I learned while testing on the non-production databases first.

Results so far

True to "stability first," these come from non-production instances first (both now complete), before production:

One of our non-production databases: used space from 9 TB to 2 TB, and allocated from 9 TB down to 2.2 TB — bringing allocated right down to what's actually used, with just a small buffer for growth.
Another instance: used from 6 TB to 3.2 TB; allocated from 6 TB to 3.5 TB.
Production: used space is already down from 18 TB to 11 TB, and the production shrink is now running — we expect it to reclaim a little under 7 TB (a bit less than that, since we deliberately keep a buffer), bringing allocated space back down close to what's actually in use.

A note on backup cost

Backup cost, roughly speaking, has two parts: recent (short-term) backups and long-term retention. As the database got smaller, the recent backups shrank with it almost immediately. The long-term retention copies, though, are full backups taken from the larger database — they'll come down only gradually, as older backups age out under the retention policy and get replaced by new ones taken from the smaller database. So part of the saving is already here, and part will arrive over time.

Longer term, there's a plan already taking shape to move long-term-retention backups off the Azure-managed service and onto infrastructure we run ourselves — what's often called cloud repatriation, a broad and genuinely interesting topic in its own right — which should reduce backup cost further. That part will be delivered by our Systems Engineers Team, and implementation is expected to start soon. Shrinking the database helps here too: the smaller the backups, the less there is to migrate and manage — and the cheaper it all is to keep.

The bigger picture

None of these levers works alone. Automatic index maintenance, removing unused indexes, the ongoing refactoring, and partitioning together should reduce the database's size substantially — as a rough order of magnitude, to somewhere around 4–5 TB, perhaps less (hard to pin down precisely yet) — and, just as importantly, make its growth controlled rather than accelerating. The refactoring will also move that external client I mentioned earlier in the series onto our API instead of a direct connection, which means we can eventually retire its read-only replica — cutting infrastructure and operational cost further, and lowering the total cost of ownership of the whole setup — the database and everything around it.

Next, the part that tends to spark the most curiosity: where AI actually helped — safely.

Originally published as a LinkedIn post series, "The Database Marathon."

Optimizing an 18 TB Azure SQL Hyperscale Database — Part 3: The Real Cost of Indexes

Kostya Bartashevich — Tue, 04 Aug 2026 13:17:59 +0000

Previously: we halved compute by understanding a spiky load profile. Cutting CPU was only half the picture — the database's size and its indexes were the other half.

Two things about fragmentation stood out early. First, there was no regular index maintenance job at all, so fragmentation simply accumulated. Second — and more fundamental — several of the largest, busiest tables used a random uniqueidentifier (GUID) as their clustered primary key.

Here's why that matters, briefly. A clustered index defines the physical order in which rows are stored. With a random GUID, new rows don't land at the end of the index; they land in arbitrary positions, which forces page splits and constant fragmentation. On these tables, even a full defragmentation was undone within hours under load. To be fair, a GUID clustered key isn't always a mistake — but for large, high-insert tables, a random clustered key has real consequences (and a real price). And, as I said at the start: this is evolution, not criticism — the original choice had its own context.

How we tackled it

The strategic answer, which we're working on now, is to refactor and migrate these tables to a partitioned design. Partitioning lets index maintenance happen per partition instead of rebuilding a multi-terabyte index in one piece — and it should also let us drop some indexes altogether, since partitioning can take over work they currently do. This is a strategic task, not a quick one, precisely because of the scale: even today, after the cleanup, the five largest time-series tables together hold around 10 TB including indexes and still aren't partitioned — big enough that maintaining them means heavy, manual, resource-hungry work rather than something we can automate.

But we needed relief sooner, so we started with the safe, quick wins. First, I set up and configured automatic index maintenance for the smaller indexes and rebuilt them — cheap, low-risk, and no need for much free space. Then came a step that unlocked everything after it: we removed a few large, unused indexes (more on how we chose them below), which freed several terabytes. That mattered for more than cost — before it, used space almost equalled allocated space, so a big rebuild would have had nowhere to work without inflating the database's allocated size (the very number we were trying to bring down). Clearing those indexes first created the headroom the rebuilds needed, and avoided that inflation — there's more on this in the next part.

With that room available, we could rebuild the large non-GUID indexes. Even this was a multi-week effort — these indexes are huge — run as resumable index rebuilds during off-peak hours, watching for blocking and resource pressure as they went.

The large GUID-based clustered indexes were a harder case, and rebuilding them only made sense after fixing their root cause. The truly correct fix would be to move off these GUID keys altogether — and we will, once the tables are refactored and partitioned. But that's a large, invasive change, and we needed something faster that would also free up space. So, as an interim step, we changed how their key was generated: we let the database produce a NEWSEQUENTIALID value at insert time instead of the application assigning a random GUID beforehand, and refactored the stored procedures and the C# / Entity Framework code accordingly. New rows now write compactly toward the end of the index, sharply reducing page splits. Only then was it worth rebuilding these indexes — otherwise the random key would have re-fragmented them almost immediately. That was another multi-week effort, one index at a time, each running for days.

There was a final, related optimization. After moving to sequential keys, I looked at wait statistics and saw PAGELATCH_EX waits — classic last-page insert contention, which sequential keys can introduce under very high concurrency. So on the hottest table I enabled the index option OPTIMIZE_FOR_SEQUENTIAL_KEY, and once the effect was confirmed, applied it to other suitable tables. Neither change is the whole answer on its own — but together they added up.

Choosing which indexes to remove

Deciding what to drop needed care: dropping the wrong index is how you cause an outage. We found roughly 160 indexes that appear unused, but we did not drop them all at once. Stability comes first, and some are genuinely seasonal — a few are tied to invoicing or financial periods and only get used a couple of times a year. The ones we removed were the largest and most clearly safe, and even then we checked each carefully (one had been built for a specific client, so we confirmed with them).

The rest of the unused indexes were a different case. Together they came to under 5 GB — negligible on storage at this scale, but still overhead on every write — so instead of dropping them outright, we set up multi-month usage monitoring, snapshotting index usage every week or two, so that we build a conservative drop list only for indexes that are truly unused. Crucially, the monitoring has to span at least one full invoicing period, since some indexes only wake up then. It's been running for over a month and still lists 150+ drop candidates.

There's a broader point here: an index is not just disk space. Every index adds overhead on INSERT, UPDATE, and DELETE, because the engine has to keep it current. Removing an unneeded index gives you two wins — less storage and cheaper writes. Creating an index for every query is a tempting instinct, and the wrong one — and so is blindly following automated tuning advice (the Azure portal, for instance, will happily suggest a new index to speed up a query). Each index has a cost, so the goal is the right indexes, not the most; a recommendation is a starting point for judgement, not a command to follow.

We also looked at the quality of an index's usage, not just whether it's used at all: seeks and scans versus updates. An index used once a month but updated millions of times a day is economically wrong — the cost of maintaining it dwarfs its value. Such an index should be changed, dropped, or replaced by another way of getting the same result.

A concrete example

One index existed to support the SSIS synchronization I mentioned earlier — the process that keeps this database in step with another of our systems. To compare the two sides, the sync reads rows from our table, checks them against the source by a set of rules, and writes back the differences; this index was there to make the read-and-check step on our side efficient. It was about 3 TB, nearly 100% fragmented (even defragmented it would be 1.3 TB), and updated millions of times a day — which is what first caught our eye. This one our Team Leader and I worked through together, from spotting it to shipping the fix. And the key insight wasn't a tuning trick; it was business context. The table holds roughly 16 billion rows, but only about 49 million of them — well under 1% — actually needed to be synchronized. We didn't need to index everything; we only needed to index the rows that participate in the sync. A new filtered index brought it from 3 TB down to under 5 GB.

And there was a nice side effect. Fixing this on the database side made the synchronization itself faster and more stable — fewer crashes, less load while it runs. Database optimization often reveals improvements well beyond the database layer.

Next: reclaiming the space itself — shrink, storage, and backup cost in Hyperscale.

Originally published as a LinkedIn post series, "The Database Marathon."

Optimizing an 18 TB Azure SQL Hyperscale Database — Part 2: Smoothing the CPU Load

Kostya Bartashevich — Thu, 30 Jul 2026 13:20:26 +0000

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."

Optimizing an 18 TB Azure SQL Hyperscale Database — Part 1: Context & Principles

Kostya Bartashevich — Tue, 28 Jul 2026 20:46:27 +0000

Before we start

This is a series about the intermediate results of an ongoing effort, not a finished story. It isn't an academic paper — it's a record of real engineering work and the insights that emerged along the way. Also, it's not about AI generating code. The AI angle here is about investigation and research — a careful, governed use of AI as a tool, not an autopilot — something I'll come back to in the final part.

A word on why now, with the project still unfinished: details fade — the small technical decisions, the intermediate observations, the context in which a given call was made. Writing this down while the work is still ongoing is partly how I keep that context from slipping away. And that context matters: it's a reminder that every past decision, mine or anyone else's, was made for reasons that made sense at the time.

One more note: none of this happened instead of product work. All of it ran alongside building new features and fixing bugs — the roadmap never paused for it.

On confidentiality: I don't name the Customer, and I avoid any personal data or details a competitor could use. For the same reason, I don't mention anyone by name and refer to colleagues only by role. I won't name them, but I want to acknowledge up front that much of what follows was only possible thanks to the people I work with. The numbers are approximate and rounded — the point is the order of magnitude and the reasoning, not the exact figure.

And a framing to carry through the series: at this scale, optimization is less a sprint than a marathon — yes, probably the most overused metaphor around, but here it genuinely fits: steady pacing beats sprinting, and you get there one careful step at a time.

How I ended up here

I'm a software engineer, and I've spent most of my career close to backends and databases. I've also led teams as a technical team lead — though over time I've deliberately shifted back toward more hands-on technical roles, which is where I'm most effective and most at home at the moment. I'm not a DBA by training, but databases have always interested me. Even when you work through an ORM — say, Entity Framework in .NET — you still have to understand what it's doing underneath: reading an execution plan, weighing trade-offs, knowing when the convenient option isn't the right one. We'll get to concrete examples of that throughout this series.

On any team, people naturally specialize. Databases are the area I gravitate toward, so on this project — as on earlier ones — I became the person who spends the most time close to the data layer. Over time, this particular database has become the one I focus on most — a responsibility I took on gladly and grew into with our Team's trust and support, and one I've come to treat as my own: its performance, its cost, its long-term health.

Who I work with

None of this happens in isolation. A lot of what I do is finding and framing problems on the database side — and often fixing them myself, too. I've picked up a good deal of the project's technical detail and business logic along the way. But no one person holds the whole picture, and I lean on the people who know parts of it better than I do. Our Team knows the application, its sister systems, and the business logic, and often turns a raw database observation into the right technical decision — and our Team Leader, in particular, has trusted me and given me the room to research and act. Our Data Analytics Team knows how the data is actually used and what it's supposed to mean. I rely on them all.

How I work

A few principles run through everything:

Stability first. The continuity of the business and the application always comes before optimization. Optimization is a way to improve the system, never a reason to put it at risk.
Low-hanging fruits first. We go after the low-hanging fruits before anything else — the biggest return for the least effort. There's no sense chasing a change worth a thousand CPU-seconds a month while one worth a million is still on the table.
Economic efficiency. An optimization has to make sense economically. There's no point spending a hundred hours to save a hundred euros. Every change needs a clear technical and financial justification.

One more thing — not really a technique, more of a mindset. When I run into a technical decision from the past that I'd make differently today, I almost never treat it as someone's mistake. Every decision came with its own context, constraints, and trade-offs — technical, business, sometimes organizational — that I usually can't fully see. In most cases it was the best available choice at the time. Refactoring isn't fixing other people's errors; it's just another step in the normal life cycle of any long-lived system. So wherever I describe changing something in this series, please read it as evolution, not criticism.

And a final principle — less about method than about why any of this matters. This work isn't only about cutting the bill today. The Customer's business is growing fast, and the load on this database keeps growing with it. Optimizing size and compute now is as much about staying ahead of that growth as about saving money — it's how we avoid hitting a wall later, when the same problems would be far more expensive and disruptive to fix. Cost is the visible win; headroom for the future is the real one.

The system, in general terms

This work is for one of our key Customers — a company running a large IoT platform: an ecosystem of connected systems under heavy load, with data that is mostly time-series in nature. It's a responsible, high-stakes project, and one I genuinely enjoy being part of. I work on one of its key components — an important, high-responsibility part of the platform, though far from the only one. The system is made up of many components working together, and this one is a significant piece of a much larger whole. At its core lies a database that has grown to roughly 18 TB. (Why a relational database is used for time-series data is a fair question, but out of scope here — as are client-side and backend optimizations, except where a database investigation surfaced them.)

Where the journey starts

When I joined, the component ran on Azure SQL Managed Instance. The database was still relatively small then — a few terabytes — so its size wasn't yet a problem. But it was growing, and we knew it would grow faster as the business scaled. The issue was structural: in that model, the storage ceiling was tied to the compute tier and vCore count, so more data would eventually mean buying vCores we didn't need just for space. We decided to break that dependency before it started costing us, and migrated to Azure SQL Database Hyperscale to decouple storage growth from compute. The migration itself wasn't entirely trivial — a move like this comes with its own technical challenges — but it did what we needed. That migration is where this optimization journey really begins.

Next part: smoothing CPU spikes, and how we took this database from 32 vCores down to 16 — carefully, in several steps, and with more still to come.

Originally published as a LinkedIn post series, "The Database Marathon."