Case study 02 — Performance

Two minutes

to one second

Diagnosis → stopgap → fixSQL · ASP.NET · Azure2026121 s → 1.6 s

The main search in an enterprise asset-management web app averaged 121 seconds, and the worst case took 15.7 minutes. The query was not slow because it was working hard. It was slow because it was waiting.

This is how I found that out, how I made it bearable without touching the application, and how I then removed the cause rather than the symptom.

Project facts

System: Enterprise asset-management web app on Azure
Stack: ASP.NET · Azure SQL Managed Instance
Role: Investigation, stopgap script, query rewrite, equivalence proof
Tools: Query Store · execution plans

Diagnosis

Symptom to causeWhat the data said

01 — Symptom

A keyword name search, 121 s on average.

Users typed a name and waited. Average 121 seconds, worst case 15.7 minutes.

02 — What it was not

Not fragmentation.

The obvious first move is an index rebuild. It cut logical reads by up to 80%, yet elapsed time did not change. Fewer reads with the same wait means reads were never the bottleneck. A rebuild is a treatment, not a diagnosis.

03 — The diagnosis

About 5 s of CPU for 121 s of elapsed time.

Query Store and the execution plans showed a parallel plan with three merge joins, each fed by order-preserving exchanges. Worker threads were idle more than 95% of the time. The query used about 5 seconds of CPU over 121 seconds elapsed. The work was not slow, it was stalled: the query was waiting, not working.

Response

Three movesIn order

04 — The stopgap

Reversible first, with a rollback.

I tested options against production using Query Store, then wrote a script, with a rollback, that capped database-scoped MAXDOP at 4. The DBA team applied it. Search dropped to 0.8–1.0 s in production.

05 — Tried and dropped

MAXDOP 1 everywhere.

I also tried running the whole database at MAXDOP 1, with Query Store hints giving export queries more threads. Search was 0.7–2.5 s, but exports broke, so it was not kept. A faster search that breaks another feature is not a fix.

06 — The real fix

Per-row lookups the optimizer cannot stall.

A cap on parallelism hides the stall; it does not remove it. I rewrote the view's three owner joins as per-row TOP (1) lookups with OUTER APPLY. Each one uses an index seek, so the stalling plan can no longer be chosen. The pattern, with made-up names:

-- before: a join the optimizer may turn into a merge join
SELECT i.*, o.owner_name
FROM items i
LEFT JOIN owners o ON o.item_id = i.id;

-- after: one seek per row, no ordered exchange to wait on
SELECT i.*, o.owner_name
FROM items i
OUTER APPLY (
  SELECT TOP (1) owner_name
  FROM owners
  WHERE item_id = i.id
  ORDER BY owner_name
) o;

In QA the query went from 30.1 s to 0.48 s. I deployed it to production with parallelism back to the default and every hint removed. A production spot check showed 1.6 s.

Result

Before and afterMeasured

Stage	Environment	Search time
Before	Production	121 s average, 15.7 min worst
Stopgap: MAXDOP capped at 4	Production	0.8–1.0 s
Dropped: MAXDOP 1 plus hints	Tested	0.7–2.5 s, exports broke
Rewrite, before	QA	30.1 s
Rewrite, after	QA	0.48 s
Rewrite, default parallelism, no hints	Production spot check	1.6 s

Proof and a side fix

Before shippingAnd one more

07 — How I proved it

Row by row, both directions.

A rewrite that is fast but returns different rows is a bug. Before shipping I compared the old and new view output row by row: 26 columns, zero differences in either direction.

08 — A side fix

A 191 MB report file.

A report export produced a 191 MB file because it used an old text-based .xls format. A hotfix that streams a real .xlsx brought it to 20–30 MB.

What I'd tell another engineer

Five lessonsShort list

Compare CPU time with elapsed time first. A big gap means waiting, and no amount of index work fixes waiting.
A rebuild that lowers reads but not elapsed time is a hypothesis disproved, not a result.
Ship a reversible stopgap first, with a rollback, then fix the cause.
Prove a rewrite is equivalent, row by row, before it ships.
Once the real fix is in, remove the hints and put settings back to default.

Other case studyPrompt Plus Accounting → Questions about thisContact →