The cheap detector said the site was under attack.

CPU 66-88% sustained. Connections over 900 every minute. Connection attempts above 60/sec. Slow-request count (anything over 5 seconds) hovering at 100-160 every minute, ten minutes straight. Every threshold our hourly monitor watches was breached.

This is exactly what a bot swarm looks like in the rear-view mirror. I almost ran the mitigation skill for one, which would have wasted an hour and done nothing about the actual problem.

1. What the cheap detector sees

A minute-by-minute monitor can tell you the box is in distress. It cannot tell you whether the distress comes from too much traffic or from one slow query stuck inside a normal traffic load.

The detector running on our prod box is intentionally cheap. It samples four counters every minute (CPU, current connections, attempts/sec, count of slow IIS requests in the last 60 seconds), and when any one crosses a threshold it captures a forensic bundle: requests in flight, blocking pairs, wait stats, top queries by CPU, the slow IIS log lines.

Cheap means it runs forever. It also means it doesn’t interpret what it sees. The bundle is just evidence; the diagnosis is on me.

Today’s incident bundle looked, at first glance, like a textbook swarm:

  • Sustained CPU saturation: classic.
  • Spiking connection counts: classic.
  • A flood of slow requests: classic.
  • Attempts-per-second above threshold: classic.

The reflex was obvious. Open the bot-mitigation skill. Block the offending IP ranges. Move on with my day.

I’m glad I didn’t, because that would have been the wrong answer to the wrong question.

2. The fork: swarm vs bottleneck

Cascading thread starvation looks identical to a swarm from the outside. The slow-request count climbs either way. The CPU climbs either way. The fork is hiding inside the data.

The thing the cheap detector can’t tell you is why requests are slow. There are two completely different shapes that produce the same minute-by-minute symptom:

A bot swarm is a lot of cheap requests, all to a small set of URL patterns, from a small set of IP ranges, with a homogeneous user-agent fingerprint. The CPU climbs because the box is doing 5x its normal request count. Static assets are usually fine; the bots don’t fetch them.

A SQL bottleneck cascading to IIS thread starvation is normal request volume hitting a query that suddenly costs 400ms of CPU per call. As the SQL piles up, IIS worker threads back up waiting for it. Once the thread pool is saturated, every request gets queued, including the trivial ones for CSS and images. Real users start seeing 30-second loads on static content. The slow-request count explodes.

Both look the same in the headline counters. The mitigations are completely different. The wrong one wastes ops time and doesn’t help the user.

3. The 5-minute test that resolves the fork

Three signals together tell you which shape you’re looking at: the asset mix in slow requests, the URL fan-out across customers, and the IP/ASN concentration of source addresses. Any one signal can mislead; the triangle is decisive.

The detector captures the slowest 100 IIS requests in the last 5 minutes. Three things to count, and they take about five minutes together:

Asset mix. Split the slow requests by static (.png, .jpg, .css, .js, .woff) versus dynamic (.asp, query-string-driven URLs). A bot swarm is dynamic-heavy; bots want pages, not fonts. A cascading SQL bottleneck is static-heavy, because real users are getting timed-out fetching the assets the bottleneck is starving them of.

Today: 75 of 100 slow requests were static. Bots don’t load fonts.

URL fan-out. How many unique customer sites (or pages) appear in the slow set? A swarm typically concentrates on a handful of targets. Organic load is spread across the whole customer base.

Today: 20+ distinct customer sites in 100 slow requests. No single team being hammered.

IP and ASN concentration. Count unique source IPs in a wider sample (a couple thousand log lines). Look at how the top-20 by request count distribute across /16 networks. A swarm clusters by ASN or datacenter range. Organic traffic is spread across hundreds of consumer ISPs.

Today: 547 unique source IPs in 2,000 IIS lines. The top IP was a vulnerability scanner doing 71 wp-includes probes (302s, not touching anything heavy). Real top traffic distributed across iPhone Safari, Android Chrome, Windows Chrome, Googlebot, AmazonBot.

Triangle complete. Not a swarm. Now I had to actually look at what was slow.

4. What it actually was

One stored procedure had been re-running the same slow query plan 33,000 times in 15 minutes. The supporting index existed and was correct. The optimizer just wasn’t using it.

The forensic bundle includes the top-50 queries by recent CPU. One query owned the box:

SELECT @locID = locID FROM hometeam.dbo.mapIPtoLocation
WHERE startIpNumber <= @IPdecimal AND endIpNumber >= @IPdecimal

33,444 executions in 15 minutes. 394 milliseconds of CPU per call. 3,500 logical reads per call. That’s 13,200 CPU-seconds burned on one query, on a box with cores to spare for everything else. Of course the site was melting.

I’d actually fixed this query five days earlier. We added a covering nonclustered index on (endIpNumber, startIpNumber) INCLUDE (locID) that turns the range-overlap predicate into a true seek. It dropped average CPU from 332ms to under 5ms in our local tests. That post is When a Seek Is a Half-Table Scan.

So why was it back?

I queried the cached plans for the stored procedure that wraps the query. Multiple plans existed for the same procedure: most ran at 0ms CPU and 4 logical reads (the new index, working correctly). But one plan, the one with 33,000 executions, ran at 394ms and 3,500 reads. The clustered-scan plan was still cached, still being reused for every visitor whose IP happened to hash into that plan’s bucket.

This is parameter sniffing. The optimizer compiles a plan based on the first parameter value it sees and caches it. If that value is unrepresentative (an IP near the upper end of the range table, say), the cached plan is wrong for the typical case but lives forever until something evicts it.

5. The one-line fix

Statement-level OPTION (RECOMPILE) costs 20-50 microseconds per call and eliminates the cache trap permanently.

SELECT @locID = locID FROM hometeam.dbo.mapIPtoLocation
WHERE startIpNumber <= @IPdecimal AND endIpNumber >= @IPdecimal
OPTION (RECOMPILE)

That’s it. The hint tells SQL Server: don’t cache a plan for this statement. Recompile a fresh plan on every call, using the actual parameter value. The rest of the stored procedure body still caches normally. The cost is 20-50 microseconds of compile time per call. The benefit is that the optimizer always picks the new index and never gets stuck on a bad cached plan.

I deployed it via SSMS at 22:04 ET. Four minutes later, every cached plan for the procedure was running at 0ms CPU per call. Live counters: CPU dropped from 80% to 25-45% while traffic ran twice as high as during the incident peak. Slow-request count fell from 130 to under 10. Same hardware, same code path, same prod traffic. One line.

6. The lesson

Cheap detectors are worth their cost specifically because they don’t try to be smart. They tell you the symptom and stop. The expensive part of monitoring is the human (or agent) discipline to run the right triangle test before pulling the loudest lever.

If I’d run the swarm-mitigation skill at the first alert, I would have blacklisted a bunch of legitimate IP ranges, achieved nothing for the actual bottleneck, and watched the site melt again at the next traffic peak. The IPs weren’t the problem. The SP cache was.

The triangle test costs five minutes. It is the cheapest possible insurance against expensive mistakes when your monitoring tells you something looks scary but doesn’t tell you why.

The next time the alarms go off and the symptom matches “swarm,” I’ll run the triangle first. Every time. Especially when the easy answer is sitting right there waiting to be pulled.

Related: When a Seek Is a Half-Table Scan, the post about why the supporting index for this query existed in the first place.