The Avalanche

One afternoon, a healthy production database for a busy eCommerce catalog went from a quiet 50–70 open connections to over 450 in about five minutes. The site started returning errors on roughly a quarter of requests. There was no deployment, no schema change, no flash sale. Nothing had "changed."

And yet the database was drowning. This is the anatomy of how a caching layer that works beautifully 99% of the time can become the exact thing that takes you down — and why, when the dust settled, this turned out to be a caching story, not a traffic story.

A quiet baseline

On a normal day the site is comfortable. The catalog is read‑heavy but well‑cached: the CDN at the edge serves a large share of pages without ever touching the origin, and the application keeps a second cache in memory for the pieces the CDN doesn't hold. Between the two, only a small fraction of requests ever reach a stored procedure. Open database connections sit in the dozens. Page latency is a few hundred milliseconds.

The whole system is tuned around one quiet assumption: most reads never hit the database.

The trigger: a wave of unique URLs

The afternoon's trouble started as a scraper wave — a flood of requests coming from datacenter IP ranges, spoofing ordinary Chrome user‑agents so they looked like real browsers. Edge traffic jumped to roughly nine times baseline. But raw volume wasn't the interesting part. The interesting part was the shape of it.

The scraper was walking the catalog's "browse by…" pages — browse by author, by brand, by category — crawling thousands of distinct slugs. And that single property, the uniqueness of every URL, is what turned a nuisance into an outage.

Why unique URLs walk straight past a cache

A cache is a key‑value store, and for web pages the key is (essentially) the URL. That's the whole trick: ask for the same URL twice and the second answer is free. But it also means a cache only helps for URLs that repeat. Feed it a stream where every URL is different, and every single lookup is a miss — guaranteed, by construction.

That's exactly what happened, at both cache layers at once:

The CDN. Every unique slug is a unique cache key, so nothing could be served from the edge. The edge cache‑hit ratio collapsed from ~43% to about 9%. For the busiest page type, one 30‑minute window saw over 17,000 requests at a 0% hit ratio — every one went to origin.
The in‑memory application cache. It had two problems here. It was keyed per‑slug (so the unique URLs missed it too), and it was per‑instance — each web server held its own copy — so even a repeated URL only warmed the cache on one node, not the others. Two independent shields, both keyed on the one thing the scraper was varying.

Fig. 1A cache only pays off for URLs that repeat. Give it a stream of unique URLs and every lookup is a miss — both the edge and the in‑memory layer become transparent, and requests fall straight through to the database.

Each miss was expensive

A cache miss is only as dangerous as the work it exposes. Here, the uncached render path ran the catalog's listing query — the stored procedure that fetches and ranks products for a "browse by…" page. On average that one procedure does around 17,700 logical reads per call. Worse, every page also rebuilt a shared header/navigation block backed by another procedure that averaged over half a million reads per call — the single most expensive query in the database.

At a healthy cache‑hit ratio, those procedures run rarely, and their cost is invisible. At a 0% hit ratio, with thousands of requests a minute, they became a firehose pointed straight at the database. This is the heart of it:

The failure wasn't that traffic got big. It was that traffic got shaped to route around every cache, and the path it exposed was costly.

From there it cascaded, the way these things always do. Reads piled up. SQL Server hands out connections from a finite pool, and the pool saturated — from dozens of connections to 450+. Once there's no free connection, new requests don't fail fast; they wait. Latency that normally sat around 700ms at the 99th percentile blew out past 4,000ms, and a handful of requests stalled for 30 seconds to four minutes. Sort‑heavy queries spilled into tempdb, adding contention on top of contention.

The clearest fingerprint showed up in the web server logs — a cluster of twenty‑plus completely unrelated requests all finishing in the same second:

iis.log

19:55:12  GET /browse/author/…   time-taken 28,991 ms
19:55:12  GET /product/…          time-taken 31,204 ms
19:55:12  GET /browse/label/…     time-taken 30,117 ms
19:55:12  GET /new-arrivals        time-taken 26,540 ms
19:55:12  GET /category/…         time-taken 29,880 ms
   … 20+ unrelated URLs, all finishing in the same second

That pattern — many unrelated URLs, wildly long durations, all released together — is the textbook signature of connection‑pool exhaustion. Everything was queued behind the same bottleneck, then let go at once the instant connections freed up. At the edge, about a quarter of responses were 503s and 504s. For all practical purposes, the site was down.

Fig. 2Pool exhaustion in one picture: misses arrive faster than the finite connection pool can serve them, so requests queue — and when slots finally free, a crowd of unrelated requests completes in the same instant.

Why it's a caching story, not a traffic story

It's tempting to file this under "we got hit with too much traffic." But the database wasn't undersized, and the raw request count, while high, wasn't absurd. The system had been sized around its cache doing its job. The incident happened because a specific traffic shape — thousands of unique URLs — quietly turned a 43%‑effective cache into a 9%‑effective one, and the path behind the cache was expensive enough that the difference was fatal.

That's the uncomfortable lesson: your capacity plan often isn't "requests per second." It's "requests per second that miss the cache," multiplied by the cost of a miss. Both of those numbers were hiding in plain sight until something came along and varied exactly the right input.

What came next

When the scraper wave subsided, the connections drifted back to baseline and the site recovered on its own. That's when the real work started — and it was harder than it looks. The obvious suspects turned out to be mostly innocent (two of the highest‑volume endpoints don't touch the database at all), a widely‑shared assumption about "bot traffic" was wrong, and one of the very slowest queries wasn't a cause of the saturation — it was a victim of it.

Separating cause from victim, with evidence rather than intuition, is the subject of Part 2.