Bits of .NET

🗄️ The Query That Ran Fine for Months and Then Suddenly Didn’t

A query that performs well for a long time and then degrades sharply, with no code change involved at all, is very often explained by the query optimizer switching strategies once a table’s row count crosses an internal threshold – a plan that reasonably chose a nested loop join for a small table can become genuinely wrong once that table grows large enough that a hash or merge join would perform far better, and SQL Server doesn’t necessarily pick a new plan on its own until statistics are updated or the plan is evicted from cache for an unrelated reason.

🔎 The Problem

-- A query joining Orders to a lookup-style table that started small
-- and grew steadily over many months:
SELECT o.OrderId, o.Total, c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerId = c.CustomerId
WHERE o.CreatedAt >= '"'"'2026-01-01'"'"';

-- Early on, Customers had a few thousand rows - a nested loop join
-- (looking up each order'"'"'s customer one at a time) was genuinely the
-- fastest plan available, and the optimizer correctly chose it.
--
-- A year later, Customers has grown to several million rows. The exact
-- same query, still using a cached nested-loop plan from when the
-- table was small, is now doing millions of individual index lookups
-- instead of a single efficient hash join - and nothing about the SQL
-- itself changed at all to cause it.

✅ Fix: Keep Statistics Current, and Watch for Plans That Stopped Fitting

• Confirming auto-update statistics is enabled (and, for a large, fast-growing table, considering a more frequent manual UPDATE STATISTICS on a schedule) ensures the optimizer has an accurate row-count estimate to base its join strategy on, rather than working from stale statistics that still describe the table as it was much earlier.
• Clearing the specific cached plan (or the whole plan cache, in a maintenance window) forces a fresh compilation the next time the query runs – if the newly compiled plan performs meaningfully better with current statistics, that confirms a stale, no-longer-appropriate cached plan was the actual cause rather than the SQL or the schema itself.
• Query Store, if enabled, keeps a history of plans actually used for a given query over time – comparing the plan from before the slowdown against the one in use now (specifically looking at whether the join type changed) is the most direct way to confirm this exact mechanism rather than guessing at the cause from execution time alone.

⚠️ Why This Looks Like a Mystery When It First Appears

• Nothing about the query, the schema, or the application code needs to change at all for this to happen – the table simply grew past a threshold where a previously-correct plan choice quietly stopped being correct, which makes ‘what changed?’ a genuinely hard question to answer just by looking at recent deployments or commits.
• A plan sitting in cache can persist for a surprisingly long time without being recompiled, especially on a server that isn’t restarted often and doesn’t experience the kind of memory pressure that would otherwise evict old plans – so the actual moment the table crossed the threshold and the moment the slowdown becomes noticeable can be separated by weeks or months.