Every engine you run went AI-native in 2025–26, and every AI tuning suggestion is now one paste away. This module installs the rule that keeps you employable and your instances safe: AI is a hypothesis generator, never an authority — and every hypothesis earns a before/after execution plan before it ships.
The 2025–26 wave landed directly on the DBA's desk: SQL Server 2025 shipped native vectors and a GA Copilot inside SSMS 22, Oracle 23ai bundled AI Vector Search and Select AI at no extra charge, and pgvector became default-enabled on every managed Postgres. The engines themselves now generate tuning advice. What the vendors did not ship is the discipline for deciding when that advice is wrong — which is exactly the skill that separates a DBA who gets automated around from one who becomes the org's verification layer. This module teaches the tuning workflow, the triage patterns, and the failure catalog.
Rajesh is a senior SQL Server DBA at a Chennai-based logistics company: a 2 TB order-tracking database, 4,000 writes per second at peak, a change board that meets Thursdays.
A report query is slow. Rajesh pastes the query into an AI assistant — just the query, nothing else — and gets back three confident suggestions, led by a covering index with six included columns.
The suggestion looks like what the missing-index DMV has been nagging about for weeks. He creates the index on the reporting replica's primary. The report gets faster. He closes the ticket.
Overnight load ran 70 minutes late. The new index added a sixth write amplification point on the hottest table in the estate — every insert now maintains it, and the batch window absorbed the cost.
He checks: the AI was never told the table takes 4,000 writes/sec, because he never gave it that. The model optimized the only thing it could see — one read query.
The advice wasn't stupid. The workflow was: no write-load context in, no before/after verification out, no hypothetical-index test in between.
Same assistant, run properly, would have flagged the write cost itself — if the prompt had contained the table's write profile, and if the index had been tested as a hypothetical before it became real. That is the whole module in one incident: the quality of AI tuning advice is a function of what you feed it and how you verify it, not of the model.
The rule: AI proposes, the plan decides. No AI tuning suggestion — rewrite, index, hint, setting — touches an instance until it has a before/after execution plan comparison and a logical-reads delta attached to it. This is the workflow Brent Ozar teaches for SQL Server and it generalizes to every engine:
EXPLAIN (ANALYZE, BUFFERS) on Postgres, actual plan XML on SQL Server), the table DDL with existing indexes, row counts and statistics freshness, and the workload context: how often this runs, and what else writes to these tables.DBCC AUTOPILOT-era tricks or a dev restore on SQL Server) show whether the optimizer would even use the proposed index — before you pay the build and write cost.End every tuning prompt with "list the assumptions you made about data distribution, write volume, and version-specific behavior." The assumption list is where the wrong advice announces itself before the plan comparison has to catch it.
A bare query with no schema, no plan, no write profile. The model guesses at cardinalities, invents plausible index keys, and optimizes the single visible query as if nothing else exists. Output looks authoritative because SQL always looks authoritative.
Actual plan with row estimates vs actuals, DDL with current indexes, stats age, engine and version, write rate on each touched table, and the business constraint ("this must not slow the OLTP path"). The model now reasons about your system — and its could-make-worse column becomes genuinely useful.
Version context matters more than people expect: an assistant that does not know you are on PostgreSQL 12 will happily recommend syntax and planner behaviors from 16. State the engine and exact version in every tuning prompt, and treat any version-specific claim — "this hint works since…" — as a claim to check against the docs, not a fact.
One query at a time is the retail version. The wholesale version is plan-cache triage: pull the top offenders from the workload and run an AI annotation pass over each. Two established routes:
sp_invoke_external_rest_endpoint (works on SQL Server, Azure SQL, RDS, Cloud SQL). You get the standard cache metrics plus a per-query advice column.The prune pass is also where the leverage is: an annotated triage sheet turns "the database is slow" into a ranked, argued work queue you can hand a change board — with the AI's false leads visibly killed, which is what earns the sheet trust.
Raw missing-index DMVs and their Postgres equivalents overstate, overlap, and never mention write cost — acting on them directly is how estates accumulate forty indexes on a table. The AI-assisted version of index lifecycle work:
The second daily win is turning raw diagnostic exports into narratives — with verification instruments attached:
| Diagnostic input | What AI produces | The verification instrument |
|---|---|---|
Wait stats / AWR / pg_stat_activity snapshot | A plain-language bottleneck story with confirming queries to run | Your baseline: is this wait profile actually abnormal? |
| Blocking chain export | Who blocks whom, the root blocker, the likely application pattern | pg_locks / live DMV check while the chain is active |
| Deadlock graph XML / log detail | The lock cycle explained, victim identified, ordering/index/isolation fixes proposed | A repro script, then the fix proven under looped concurrent load |
| Good plan + bad plan + parameter history | Hypothesis set: sniffing, stats drift, cardinality misestimate | Query Store plan forcing test, or stats refresh on a copy |
The deadlock case shows the pattern at its best: deadlock XML is dense, rarely read carefully, and a model explains the cycle in thirty seconds — but the proof is a repro script that demonstrates the deadlock, then demonstrates its absence after the fix. An explanation without a repro is a story.
Instance configuration review works the same way: dump the non-default settings plus a workload profile, ask for engine-version-aware anti-pattern flags (MAXDOP, memory grants, autovacuum thresholds, shared_buffers), and route anything you accept through change control. ML-driven parameter tuners (dbtune-class, postgres.ai) sit adjacent: they optimize continuously by experiment, which is a different risk contract than advice — treat them as a change-controlled tool you evaluate, not a colleague you trust.
SSMS Copilot, Oracle's AI features, and chat assistants all sit in the same seat in this workflow — hypothesis generator. The workflow does not change based on whether the suggestion came from inside the engine or from a browser tab.
The ways it goes wrong are a short, learnable list. Learn them and the verification loop stops being ceremony and starts being targeted:
The most dangerous AI tuning advice is the kind that works — the report did get faster for Rajesh. Partial success is what convinces teams to skip verification on the next one. The verdict table, with plan evidence per proposal, is the habit that survives success.
Scenario questions in this domain hinge on sequencing — the correct answer almost always tests the proposal (hypothetical index, staging replay, before/after plan) before production, and the trap answers act directly on AI or DMV output because it "matched the recommendation."
ai-tuning-failures.md and add the first entry: one AI suggestion you rejected this week, what it missed, and which check caught it.Check yourself: An assistant recommends a covering index and the report query it targets gets 8× faster in your test. What must you still check before the change board sees it — and what evidence goes in the request?
This module anchors LAB-01 (EXPLAIN-verified AI query tuning gauntlet), where one of the AI's confident recommendations makes performance worse and shipping it fails the lab; LAB-02 (plan-cache triage with AI annotation); and LAB-03 (deadlock autopsy with AI). Do it at work this week → pb-03 (the core tuning loop), pb-04 (plan-cache triage and prune), and pb-05 (index consolidation with hypothetical validation).
AI in tuning work is a hypothesis generator with excellent grammar. The evidence pack in, the verdict table out, and a before/after plan for every proposal — that loop is what makes the fast advice safe, and the DBA who runs it is the one the estate still needs.