--- title: "SOS_SCHEDULER_YIELD at 89% CPU and the swarm that never existed" canonical: https://dxdev.com/blog/bot-swarm-one-bad-query-plan/ datePublished: 2026-05-23 --- At 9 PM the app went sideways: CPU 89%, `SOS_SCHEDULER_YIELD` topping the SQL wait stats, slow-request count climbing. Forty minutes in we were mentally drafting a blog post about a second bot swarm from a new operator, and already assembling evidence for a story we had never questioned. There was no swarm. One cached query plan in the request-log table had been running a clustered scan on every request for hours. We nearly missed it because we were busy building the wrong narrative. ## The swarm story was already pulling The uptime monitor caught it within a minute, and the headline counters looked like a swarm. No blocking, no memory pressure, no disk wait, just CPU contention under load. Three days earlier we had spent a morning blocking five `/19` ranges from a proxy reseller, and that incident had produced exactly this signature. The agent session was still warm from it. We opened a Claude Code session, dropped in the new incident bundle, and asked for a read. The answer came back as a traffic-driven load pattern, possibly a new swarm, with a recommendation to check IIS logs across the app's sites. The next step was reasonable, but the starting assumption was wrong. The prior session was still in context, so Claude had already seen the proxy reseller incident: the same wait signature and the same path from CPU spike to firewall rule. It reasoned forward from that pattern before checking whether the pattern fit. We would have made the same call, because nothing in our process forced a distribution check first. So we pulled the IIS logs for the past hour and grouped source IPs by `/16` netblock. The distribution was normal. No block dominated the count, there was no enumeration URL pattern, and the user agents were not homogeneous. It was regular traffic at elevated volume. The question changed from who is attacking us to why the database is burning CPU on normal traffic. ## What the request-log table was doing Every request on the site runs a [bot-filter check](/everyday-ai/the-filter-that-couldnt-tell-us-from-an-attacker/) against a central request-log table. It is a single SELECT to see whether the source IP is blocked or flagged. At normal volumes it costs nothing noticeable. At several hundred requests per second, the plan it uses starts to matter. We pulled `sys.dm_exec_cached_plans` joined against `sys.dm_exec_query_stats` and sorted by total worker time. One statement appeared twice in the plan cache under two different plan handles, with the same text and two very different execution profiles. The fast plan averaged a fraction of a millisecond with a handful of logical reads per execution. The slow plan averaged orders of magnitude more, with thousands of logical reads and a clustered index scan. The slow plan was winning the majority of executions. This is [parameter sniffing](/blog/sql-server-parameter-sniffing-looked-like-a-bot-swarm/). When SQL Server first compiled the statement, it built a plan for the parameter value in use at that moment. That value had high cardinality in the request-log table, meaning lots of matching rows, so the optimizer chose a clustered index scan. A scan is efficient for large result sets and catastrophic for IPs that match nothing at all. The plan got cached. Every later IP lookup, including first-time IPs with zero filter history, ran a full clustered scan instead of a seek that would have finished in microseconds. The box was paying for the wrong plan on every single request. ## The one-line fix When you can't wait for natural plan eviction, the immediate fix for parameter sniffing is `OPTION (RECOMPILE)`. It forces a fresh compilation on each execution using the actual runtime parameters. That costs more CPU per execution but gives a correct plan instead of a catastrophic one. We opened SSMS, found the request-log lookup in the data access layer, added `OPTION (RECOMPILE)`, and pushed the change as a hotfix. The post-deploy sample showed roughly double the request rate at half the CPU. The slow-request counter for the trailing 60 seconds came back to baseline within a minute. ## Why the swarm story won Here is the part that bothers us. We knew the checklist for "is this a swarm" before we started. We had written it down after the proxy reseller incident three days earlier: asset mix, UA homogeneity, Referer presence, traffic distribution. Those are the signals. CPU load and wait type are not. The checklist sat in agent memory, in a feedback file we wrote ourselves. We did not run it until forty minutes in, when we caught ourselves drafting the wrong blog post and asked Claude to check distribution instead of IPs. Claude made the same error for the same reason. The prior session was still in context and the surface signals matched, so the agent was pulled along just as we were. We had a narrative that fit the evidence well enough that we stopped generating alternatives, and the checklist written to prevent exactly that did not fire on its own. A second reason the swarm story pulled so hard is less flattering. A swarm makes us the victim instead of the author. "Some scraper is hammering us" reads a lot better than "we shipped a slow query." That is why a diagnosis that exonerates you deserves the hardest scrutiny. Acting on this one would have meant blocking netblocks that belonged to paying customers who had done nothing wrong. The checklist needs a trigger as well as a place to live. Memory that does not surface itself at the moment of decision is decoration. ## Why the cheap detector could not tell the difference The detector on the production box is deliberately cheap. It samples four counters every minute: CPU, current connections, attempts per second, and the count of slow IIS requests in the last 60 seconds. When any one crosses a threshold, it captures a forensic bundle. A monitor like that 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 inside a normal traffic load. We almost ran the bot-swarm mitigation skill, which would have wasted an hour and done nothing about the problem. ## The open question we are carrying `OPTION (RECOMPILE)` on a statement running thousands of times per minute means thousands of recompiles per minute. On modern hardware the per-compile overhead is small but not zero, and it is not the right long-term answer. The correct fix is probably a plan guide or a targeted filtered index that lets the optimizer choose seek or scan from the actual row distribution instead of compile-time assumptions. We have not built that yet. The follow-up is on the list, and the recompile hint is a one-line hold so the incident does not recur while we work out the right index shape. We are labeling it a temporary fix on a list of temporary fixes, which is fine. The site is running, and the right answer does not need to ship today for the current fix to be correct. What does need to ship is the trigger. A checklist that only fires when we remember to invoke it has the same shape as the cached plan that got selected once and then ran for hours. It is correct on paper, wrong in production, and invisible until somebody pulls the cache and looks. Before blocking any traffic, confirm it is hostile from its distribution (netblocks, user agents, URL patterns), not from CPU load alone. Then ask where your incident checklist is written down, and what makes it run at the moment of decision.