It Looked Exactly Like a Bot Swarm. It Was SQL Server Parameter Sniffing.

The CPU on the production box climbed with traffic, page loads stretched out, and my first instinct was the one every operator reaches for: someone is hammering us. A traffic-correlated CPU spike is the classic shape of a scraper swarm pinning your server, and I run a Windows/IIS/MSSQL SaaS that takes real scraper traffic, so the pattern matched something I had seen before. I almost started blocking netblocks.

The diagnosis was wrong. The cause was a cached SQL Server execution plan that was correct for the input distribution it was compiled against and badly wrong for the inputs it was now serving. One query hint fixed it. “It’s a bot attack” and “it’s parameter sniffing” produce nearly identical symptoms, and the emotionally satisfying one is the wrong one.

Why “bot swarm” feels right

A bot swarm and a sniffed plan share the same surface signature. CPU rises in lockstep with request volume. Response times degrade as load climbs. The database server, not the web tier, is where the heat shows up. Everything points at “we are out of capacity, and the extra load is the cause.”

That reading is satisfying because it externalizes the problem. It’s not your code, it’s some scraper in a residential proxy network. You get to be the victim instead of the author of the bug. A diagnosis that makes you the innocent party is the one you should check hardest before you act on it, because acting on it means blocking IP ranges that might belong to real customers.

So before I touched a firewall rule, I ran the test that separates a real swarm from everything that merely looks like one.

The three-signal triangle

IP range bad-rate, the obvious metric, is useless here. It misses residential-proxy swarms entirely because the traffic is spread thin across thousands of clean-looking residential IPs, and it produces false alarms on legitimate traffic that happens to cluster. To actually call a swarm you need three signals pointing the same direction at once:

1. Asset-mix. What file types are being requested? Real browsers pull HTML and then a tail of CSS, images, fonts, and scripts. Bots usually request only the HTML or only the endpoint they care about and skip the static assets. A request stream that is all dynamic pages and no CSS or images is a tell.

2. User-Agent homogeneity. Real user traffic has a messy spread of User-Agent strings: a dozen Chrome versions, Safari, mobile, the occasional ancient browser. A swarm tends toward a narrow band of identical or near-identical UAs, because it’s one toolchain fanned out across many IPs.

3. Referer chains. Real users arrive with plausible Referer chains: a search result, an internal link, a bookmark with no Referer mixed in. A swarm hitting deep URLs directly with no coherent Referer history is moving in a way humans don’t.

Any one of these can be spoofed or can look weird for innocent reasons. The discipline is that all three have to agree before you call it a swarm. One signal is a hunch, three signals is a verdict.

I ran the triangle against the live traffic during the spike. The asset-mix looked like real browsers, full page loads with their tail of static assets. The User-Agent spread was diverse, the normal messy human distribution. The Referer chains were plausible. Three signals, none of them said swarm. The traffic was just my actual customers, behaving normally, in normal volume.

Which left a much more uncomfortable question. If the load is normal, why is the load suddenly expensive?

Following the cost into the database

When the request volume is ordinary but the per-request cost has jumped, the spike isn’t coming from outside. It’s coming from something on the inside getting slower per unit of work. On a SQL-backed app that points straight at the database, so I went looking for which query had gotten expensive.

The culprit was the path that touches a hot-path filter table the app reads against on every request as part of its IP-filtering logic. That’s the cruel part of the masquerade: because the expensive query rides on the request path, its cost scales with traffic. More requests, more executions of the now-expensive query, more CPU. The cost curve tracks traffic perfectly, which is exactly the shape you’d expect from a capacity problem or a swarm.

What parameter sniffing is and why it bites here

SQL Server compiles a plan for a parameterized query the first time it runs, sniffing the parameter values from that first execution to estimate how much data the query will touch. It caches that plan and reuses it for every later call, regardless of what parameters those calls pass.

That’s a good optimization when the data distribution is even. It’s a trap when the distribution is skewed. If the plan gets compiled for a parameter value that matches a handful of rows, the optimizer picks a strategy tuned for “few rows,” something like nested-loop seeks. Then a later call comes in with a parameter value that matches a huge slice of the table, and SQL Server runs that small-result plan against a large result anyway, because the plan is cached.

That is what was happening. The cached plan was correct for some input and badly wrong for the actual distribution the table was seeing, and because it stayed cached, every request on that path paid the inflated cost. Nothing about the traffic had changed; the plan had.

The smoking gun is sys.dm_exec_cached_plans joined against sys.dm_exec_query_stats, sorted by total worker time. Two plan handles for the same statement text with wildly different execution profiles is parameter sniffing confirmed.

The fix: one query hint

The fix was OPTION (RECOMPILE) on the offending query. That hint tells SQL Server to compile a fresh plan on every execution and skip the plan cache for that statement, so it sniffs the actual parameters of each call instead of reusing whatever got cached first. The trade-off is real: you pay a small compilation cost on every run rather than amortizing one plan across many calls. For a query whose right plan genuinely depends on the current parameters, that recompile cost is a rounding error next to the cost of running the wrong plan thousands of times.

It deployed in minutes. Response times dropped back to baseline immediately. No netblocks blocked, no customers locked out, no firewall rules to unwind later.

If I had trusted the first instinct, I’d have spent the afternoon blocking IP ranges, the spike would have stayed, and somewhere in those ranges I’d have blocked a paying customer for a problem they had nothing to do with.

OPTION (RECOMPILE) is a hold, not the permanent answer. Thousands of recompiles per minute at scale adds up. The correct long-term fix is usually a plan guide or a filtered index that lets the optimizer choose seek vs. scan based on actual row distribution. Ship the hint to stop the bleeding, then fix the index shape when you have time.

Related

• The Index Seek That Was Actually a Half-Table Scan (5000x, on a 4M-Row geoIP Table): another SQL Server “looks cheap, isn’t” trap, an index seek that did half-table work
• The Index Seek That Was Actually a Half-Table Scan, 5000x on a 4M-Row geoIP Table: alternate angle on the same half-scan class of query-plan mismatch
• The Prod Box Was DDoSing Itself: An iCal Calendar Feature Looping Through the Public Edge: traffic-shaped spike with an internal cause, same “blame external before checking internal” pattern
• The detector was watching the wrong door: a 49,000-request swarm hid on the uninstrumented IIS site: the instrument gap that lets a real attack look like normal traffic
• The 3% bot attack that took the site down: why your IP blacklist can’t see residential proxies: what an actual swarm looks like when the three-signal triangle does point the same direction

dxdev LESSON · paste into your AI coding agent
LESSON: Traffic-Correlated CPU Spikes: Bot Swarm vs. Cached Bad Query Plan
SOURCE: dxdev.com/blog/sql-server-parameter-sniffing-looked-like-a-bot-swarm
WHAT HAPPENED: A production database server's CPU climbed in lockstep with request volume and response times degraded, producing the classic surface signature of a scraper swarm. The three-signal triangle (asset-mix, User-Agent spread, Referer chains) ruled out a real swarm. The actual cause was a SQL Server execution plan cached against a low-row-count parameter value, then reused for high-row-count inputs on every request, making per-request cost scale with traffic in a way indistinguishable from an external load problem.
THE RULE: Before blocking traffic in response to a traffic-correlated spike, verify all three swarm signals agree and check whether per-request query cost has silently increased.
CHECK MY CODE, then report PASS / FAIL with file:line for each:
  1. Any parameterized query on the hot path that touches a table with a skewed value distribution (few rows for some inputs, many rows for others) where the query has no OPTION (RECOMPILE) or plan guide.
  2. Any incident-response runbook or on-call procedure that jumps to IP blocking or rate-limiting before querying sys.dm_exec_cached_plans / sys.dm_exec_query_stats for cost anomalies.
  3. Any traffic-analysis or anomaly-detection code that uses only IP-range bad-rate as its swarm signal without checking asset-mix homogeneity or User-Agent spread.
THEN PRINT: a table (check, PASS/FAIL, evidence, fix) + a verdict (applies / partially / OUT_OF_SCOPE / no) + the single most important next action.