Finding the query behind a database spike used to take days. The person investigating was exhausted, and the person fixing it was even more exhausted. Now? A few hours — and as a bonus, I actually learned new things along the way.
Here's the story. If you've ever worked on an application that uses an ORM (Object-Relational Mapping — basically an "auto-translator" between your code and the database), you've probably experienced this: the database suddenly slows down, you get the raw query that's causing trouble, but in your codebase you write using ORM syntax that looks nothing like that raw SQL.
For those who haven't dealt with an ORM before, think of it this way: you write a message in English, and there's an "auto-translator" that converts it to Japanese before sending it to the recipient. One day there's a problem with the delivered message — but you can only see the Japanese version. Finding which part of your original English text caused the translation issue? That's the kind of effort that makes you want to go back to bed.
Now with Kiro, I just provide the raw query + codebase access, and it automatically finds which part of the code generates that problematic query. What used to take days now finishes in hours — and that's just the investigation phase, not even the fix.
How I Got Access to Kiro
I've been actively using Kiro at work lately. Earlier last year, our company received credits through the Kiro for Startup program, so we figured we'd make the most of it.
Besides debugging and exploring queries on MS SQL Server, I sometimes use Kiro to analyze AWS CloudWatch logs — while providing application context so the analysis is more accurate and not generic.
In this post, I want to share how I've been using Kiro as a partner over the past few weeks to improve query performance in our .NET Core application. Why "partner"? Because Kiro isn't allowed direct access to the database — it has to go through me. We discuss, collaborate, and solve together. It's not an AI that gets handed a button and told to run on its own.
When the Database Started Screaming
A while back, our database metrics showed alarms — data I/O was spiking frequently. This needed to be investigated and improved before users started feeling the impact.
Like I said earlier — this was the work I dreaded the most. The complexity is at the level of: you only get raw queries generated by the ORM, while in the codebase you write using syntax that looks completely different. You can't immediately tell which code is generating the slow query.
For a simpler picture: an ORM is like telling an assistant "get me the active orders, sorted by most recent." The assistant translates that instruction into technical commands for the database. If their translation turns out to be inefficient and makes the database slow, you have to reverse-engineer from that technical output back to your original instruction — and that's what makes it painful.
The Flow: Kiro Can't Touch the Database
Since the database can't be given direct access to AI, the flow works like this: Kiro tells me what queries to run, I execute them on the database, and I send the results back for analysis.
I started by giving context:
"I have an MS SQL Server database that spiked in data I/O a few minutes ago. We're going to find out which process/query caused this. Tell me what queries I need to run, I'll execute them and send you the results to analyze."
From there, Kiro gave me several diagnostic queries to run:
Top I/O queries — queries with the highest physical reads/writes
Currently running I/O heavy — queries running during the spike
I/O stats per database file — which files have the most I/O
I/O wait stats — dominant I/O-related wait types
Current indexes — all existing indexes in the database
Index usage stats — how often indexes are used vs updated
Missing indexes — index recommendations from SQL Server itself
Index fragmentation — fragmented indexes can cause excessive I/O
Resource stats — historical resource usage (Azure-specific)
I/O stats per table — which tables produce the most I/O
After I ran everything and sent the results, Kiro immediately analyzed and delivered a comprehensive output — from executive summary to action plans by priority.
The Analysis: Not Just "Your Query is Slow"
Kiro provided findings with severity levels:
| Finding | Severity |
|---|---|
| Very high read latency (130-169ms avg) | 🔴 Critical |
| 86% wait time = PAGEIOLATCH_SH (read I/O | 🔴 Critical |
| Missing index on primary filter columns | 🔴 Critical |
| 3 indexes never read (0 reads, 24K+ writes) | 🟡 Warning |
| Eager loading before pagination in ORM | 🔴 Critical |
What's interesting is it didn't stop at the database level. It immediately identified the problematic code pattern:
csharp
// Problematic pattern found by Kiro:
var data = _db.Orders
.Include(a => a.OrderItems) // ← Eager loads ALL items first
.AsNoTracking()
.Where(x => x.TenantId == tenantId && !x.Deleted)
.OrderByDescending(x => x.UpdatedAt)
.Skip(page * size)
.Take(size)
.ToListAsync();For non-.NET developers: .Include() is like saying "also grab all the related data while you're at it." The problem is it's placed before pagination — meaning the database loads all data plus all its relations into memory first, then cuts it per page. If there are millions of rows? Out of Memory.
Kiro recommended a 2-step fix approach:
csharp
// Step 1: Get just the IDs first (lightweight, no relations)
var pagedIds = await _db.Orders
.Where(x => x.TenantId == tenantId && !x.Deleted)
.OrderByDescending(x => x.UpdatedAt)
.Skip(page * size)
.Take(size)
.Select(x => x.Id)
.ToListAsync();
// Step 2: Load full data + relations only for the paged IDs
var result = await _db.Orders
.Include(a => a.OrderItems)
.AsNoTracking()
.Where(x => pagedIds.Contains(x.Id))
.ToListAsync();The concept is universal: don't load everything first then cut — cut first, then load what's needed. This applies to any framework or language that has an ORM.
Besides code-level fixes, Kiro also provided database-level action plans:
Create missing index for columns frequently filtered but not yet indexed
Drop unused indexes that are never read but constantly updated on every write (cost without benefit)
Disable Azure auto-index which sometimes creates overhead by generating indexes that end up useless
Why the Results Are Different from Just Pasting a Query into AI
Looking at the action plan above, Kiro doesn't just analyze slow raw queries and give generic advice like "add an index." What makes the difference: it also studies the .NET Core backend code that generates those queries.
So for example, from a slow raw query, it can trace on its own — "Oh, this query is generated from OrderController.GetDataTableParams, line such-and-such, in repository method GetOrdersWithDateFilter." I don't need to tell it which file — it searches the codebase itself.
From there, the suggestions become specific to our situation, not template answers:
Queries that need tuning aren't just from the SQL side, but also from how the ORM generates them. For example, the
.Include()positioning that causes the database to load millions of rows before pagination kicks in — that's a code-level refactor suggestion, not a database-level one.There's an Azure feature that turned out to be counter-productive in our setup: auto-index was active but kept creating indexes that were never read, adding ~47K write cost updates with zero benefit.
The indexes it suggested dropping weren't guesses. It cross-checked from usage stats (0 reads, tens of thousands of writes) and confirmed that no query in the codebase actually needs those indexes.
This is what's different from just giving a single query snippet to AI and asking for opinions. Because Kiro has access to the entire codebase, it can connect the dots — from "slow query in the database" to "the code pattern in the application that generates that query" — and provide solutions that address both sides simultaneously.
AI Doesn't Make You Dumber — It Became a Learning Partner
Something I hear often: "AI makes us dumber and lazier to learn." My experience in this case was the exact opposite.
Here's what happened. When Kiro found problematic patterns in the codebase, it didn't just say "this is wrong, change it to this." It explained why it's wrong, what happens behind the scenes, and when that pattern is actually acceptable. And me — someone who's been writing .NET code for years — only then realized some fundamental things I'd been overlooking.
Join the conversation! Your thoughts help the community grow.