The situation. A SQL Server instance running on AWS RDS was working harder than it should have. A handful of queries ran long and heavy — pinning resources, dragging on the application, and getting slowly worse as data grew. On RDS you can't just throw a bigger box at it forever without the bill noticing, so the real question wasn't "how much hardware do we buy," it was "where is the time actually going."
What was at risk
Resource-intensive queries don't stay contained. One long-running statement holds resources the rest of the workload is waiting on, so a problem that looks like "one slow report" shows up to users as "the whole app is slow." Left alone, the usual response is to scale the instance up and pay more every month to paper over a query problem — which fixes nothing and hides the cause.
Diagnosis before the fix
The tempting move is to start adding indexes and hope. I didn't. First I measured: which queries were actually the expensive ones, what they were waiting on, and why the plans they were getting were the wrong ones. That's the difference between "add an index and see" and knowing exactly which index, on which query, and why.
The picture that came back was a familiar one — a small number of long-running, resource-heavy queries doing far more work than they needed to, with plans that weren't being reused efficiently, so the same expensive work was being recompiled and repeated instead of cached and reused.
What I did
- Identified and optimized the worst offenders — the specific long-running, resource-intensive queries carrying the load — rather than tuning everything and hoping.
- Reworked index strategy for those queries, so the engine could stop scanning what it should have been seeking.
- Improved query plan reuse — getting good plans cached and reused instead of recompiled — which cut repeated work across the whole workload, not just the queries I touched.
- Upgraded to SQL Server 2016 as part of the engagement, bringing the instance onto a current version with the newer optimizer and features behind it.
The outcome
With measured before-and-after on the target queries, the heavy statements came down and the resource pressure on the RDS instance eased, and the improved plan reuse steadied the workload as a whole. The instance stopped being a candidate for an expensive scale-up, because the problem was never really the hardware.
On RDS, tuning is cost control. Every query you make cheaper is an instance size you don't have to pay for.
More case studies: Migrating 11 databases off end-of-life SQL Server 2008 · Retiring SQL Server 2012 — 84 databases, one quiet cutover. Related guides: the SQL Server health check and taming parameter sniffing.
Application timing out, or an RDS bill climbing for no clear reason? See how the tuning sprint works, review more client results, or get in touch.