Back

Less Data, 180x Slower: A SQL Server Mystery

Nov 15, 2025

DatabaseSQL ServerOptimization

A few months back I optimized an extremely complex sales report query. It was running clean in production, consistently fast.

Last week it suddenly took over a minute to run.

  • No deploys.
  • No schema changes.
  • Same hardware.
  • Same query.

The Weird Discovery

So I started testing different date ranges and found something weird:

  • Sep 10 → Nov 15 (67 days): less than half a second
  • Sep 11 → Nov 15 (66 days): 90 seconds 😭

Removing one day made the query 180× slower. That should not be possible.

What I Checked

  • Indexes? ✔ same for both ranges
  • Data anomaly on Sep 11? ✔ normal day (~100 invoices)
  • Locks? ✔ tested off-peak
  • Hardware? ✔ unchanged

Nothing explained it.

The Real Culprit: Stale Statistics

SQL Server’s optimizer relies on statistics to estimate row counts and pick join strategies & execution plans. In my case, those were outdated… and one date range fell into a histogram step that was completely mis-estimated.

Here’s what actually happened:

For the Sep 10 start date: Old stats happened to estimate the row count reasonably. SQL Server picked a good plan: index seeks + hash joins + parallelism. Result: < 0.5s

For the Sep 11 start date: Same stale stats drastically underestimated the row count. Optimizer picked a plan meant for tiny datasets: nested loops, no parallelism. Real volume crushed it → 90 seconds.

Same table. Same query. Completely different plan because the stats were lying.

The Fix

UPDATE STATISTICS table_name;

Back to sub-second instantly.

Why It Happened

SQL Server auto-updates stats when ~20% of rows change, but:

  • Bulk operations
  • Skewed data patterns
  • Ascending keys

…can all prevent auto-update from triggering. The stats slowly became inaccurate over time, and my months-old optimization was based on fresh stats that had since decayed.

Prevention (Add to maintenance)

UPDATE STATISTICS table_name WITH FULLSCAN;

A query that runs perfectly today can fail next month because the optimizer’s mental model of your data is outdated.