When everyone has a theory, what settles it?

MY ROLE

Let me be clear about my role first

The code was written by the team's engineers, not me. I did three other things: found the root causes, decided which one to fix first, and proved with production measurements that it was actually fixed. That's also why this case is worth writing down — it's how a PM with no engineering background, in a company with no senior DBA, drove technical decisions with evidence.

CHALLENGE 01

How do you prove a cause everyone believes in doesn't exist?

The dominant hypothesis at the time was "checkout lock contention" — two orders arriving at once and locking each other. It's a reasonable theory and a hard one to refute, because "it only happens sometimes" can explain away any counter-evidence. I ruled it out with three independent layers of evidence:

1. Code: grep the entire checkout path — BeginTransaction appears 0 times. No transaction, no conditions for a deadlock.
2. Live database state: query current blocking — 0 blocking sessions, 0 lock waits.
3. History: pull seven days from Query Store — cumulative lock waits on checkout-related queries: 0.

All three pointed the same way: architecturally, it can't happen. Proving something doesn't exist is harder than proving something does — so I didn't use "I can't measure it" as the conclusion, I checked with three methods that don't depend on each other. Only once this was off the table did the real culprit surface.

CHALLENGE 02

How slow does slow have to be before it's worth operating on?

For evidence that "checkout is slow", I first wanted to use my own stopwatch timings — and then threw them out. A number I measured in the office only represents that moment, that network, that machine. It wouldn't convince anyone, and it couldn't carry the weight of a decision like "touch the payment path".

What I used instead: pull every "checkout problem" ticket customer service actually received over a period, and plot them against the database CPU timeline. Six complaints — every single one landed inside a CPU spike. This kind of evidence has two advantages: it's real customer pain, not my perception; and when incident timestamps line up with resource metrics, the direction of causality holds.

ROOT CAUSES

Pinning down each root cause

Survey query (153 seconds): not a query-writing problem, a data-structure problem. The old approach was WHERE Reply LIKE '%score%' — every query scanned the full text of every review, because the score was buried inside JSON and computed at read time. That's exactly why the two earlier fixes ("add a view column", "add caching") could only treat symptoms. The fix: compute the score at write time, break it out into its own column and table, and switch over in stages with dual-write plus backfill. This was the only fix that touched the underlying structure — and the only one that actually cured it.

Checkout payload SQL (3,000ms): one line of code, a 20,000× difference. The LINQ was written as Where(w => w.OrdersId == X || w.AddOrderId == Y), and that OR meant the database couldn't use either index and fell back to a full table scan. The fix is tiny — split it into two indexed queries and merge — and the result is 3,000ms down to 0.13ms.

Batch shipping-label printing (9.3 minutes): signed off against real print logs. Comparing Mongo's print log — 721 real prints before, 486 after — the median dropped from 4.8 seconds to 1.1.

CHALLENGE 03

21 optimisations — how do you know which one worked?

There weren't just three problems — the full list ran to 21. But shipping them all at once means you can't attribute anything: change twenty things, the system gets faster, and you never know which one did it or which was wasted effort. I ranked the 21 by risk and expected impact into three waves, measured after each wave shipped, and only pushed the next once the cause-and-effect held.

The checkout fix touched the payment path, so it also went through a full test matrix: five payment routes (web saved card, web LINE Pay, web re-payment, app LINE Pay, app ECPay) verified one by one before it hit production.

RESULTS

Results

Post-release: 8 measured saved-card checkouts averaged 5.06 seconds with zero timeouts. Before the fix, the same test would hit the 30-second timeout outright.

Every data source is labelled: performance numbers from Azure Query Store, print numbers from Mongo's print log. The only estimate is "monthly CPU total before", because the raw record had aged out of the 30-day retention window — and that's exactly how I wrote it in the report: marked as an estimate, for order-of-magnitude reference only.

AUTOMATION

Writing the judgement into automation

After the project closed I did one more thing: I wrote the "how to tell who the culprit is" I'd learned over six months into rules, and handed them to an always-on monitoring bot — when a database CPU alert fires, it automatically grabs the queries burning the most CPU right now, runs a first-pass diagnosis, and emails an analysis report to my inbox.

The most important rule in there is a trap I fell into myself: look at CPU consumption, not execution time. A query that runs long may just be waiting on locks or IO — it's the victim, not the culprit. The real culprit is the one with high CPU time. Once that rule was in the bot, my judgement became something that runs without me in the room — which is my definition of performance governance: not fixing a few queries, but making sure the diagnosis starts automatically the next time it happens.

ENCORE

Same symptom, different culprit

Two months later "checkout is slow" came back. If I'd copied last time's answer — "probably SQL again" — I'd have been wrong. Breaking it down step by step revealed two completely different things stacked together: the checkout's intermediate page had a hard-coded fixed wait, and stale database statistics were amplifying the IO tail.

LESSONS

What I learned

• "Already optimised" is not "already solved". The two earlier fixes genuinely worked and were the right call at the time; the real bottleneck was just in the data structure, and changing that was high-risk and estimated at a month-plus, so it kept getting deferred. Writing down why this attempt cured it matters more than announcing the result.
• Reports should label their confidence. In the report to my boss I split every number into "measured" and "estimated" with a note on the definition. That didn't weaken the report — it gave the measured numbers more weight.
• Don't hog the credit, but don't undersell the contribution either. The engineers wrote the code. But these three issues had been lying there for over a year before anyone diagnosed them, and nobody knew where to start. Locating the problem is itself the output.

STACK

Stack

Azure Query Store, T-SQL, SQL Statistics, MongoDB, Azure CLI, Application Insights. The diagnostic method and decision rules have been distilled into an always-on monitoring bot and an internal playbook.