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

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:

1. Observer — reads raw DMV and Query Store metrics; classifies slow queries, wait statistics, blocking, and resource pressure. It produces the Observer Report.

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

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

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

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

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

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