🗄️ Fix a Query’s Estimated and Actual Row Counts Wildly Disagreeing Because New Rows With Ascending Key Values Keep Falling Outside the Statistics Histogram’s Known Range
SQL Server’s statistics histogram for a column records the distribution of values it saw as of the last time statistics were updated, with a defined upper bound — the highest value observed at that point. For a column that only ever increases, like an identity column or a UTC timestamp recorded at insert time, every row inserted after that last statistics update has a value higher than anything the histogram knows about, and the optimizer has to guess how many rows fall in that unknown region beyond the histogram’s edge. That guess can diverge sharply from the real, growing number of rows actually there, especially on a busy table where thousands of new rows accumulate in that blind spot between each statistics update, and the resulting bad row-count estimate can send the optimizer toward a completely wrong join strategy for queries filtering on recent data.
🔎 The Problem
-- OrderDate is an ever-increasing datetime2 column; last UPDATE STATISTICS ran
-- two days ago
SELECT * FROM Orders WHERE OrderDate > '2026-11-08' -- entirely after the
-- histogram's last known value
-- Execution plan: Estimated rows: 1 Actual rows: 48,219
-- The optimizer picked Nested Loops based on the estimate of 1 row;
-- against the real 48,219 rows, that plan performs terribly
✅ Fix: Keep Statistics Current on Ascending Columns, or Tell the Engine to Adjust
- Update statistics on the table more frequently specifically for workloads filtering on recent values of an ascending column, since the size of the blind spot is directly proportional to how long it’s been since the last update.
- On SQL Server 2014 and later, confirm the improved ascending-key cardinality estimation behavior for your database’s compatibility level is actually in effect, since newer cardinality estimator versions handle this specific pattern meaningfully better than older ones.
- For a query that’s performance-critical and frequently hits this exact blind spot, consider
OPTION (RECOMPILE)so the plan is built against current data at execution time rather than a potentially stale cached estimate from before the most recent rows existed at all.
🚨 Why This Is Dangerous
- A dashboard or report querying ‘today’s data’ on a fast-growing table can get progressively worse execution plans as the day goes on and the gap between the last statistics update and the current row count grows, without any code or data structure actually having changed.
A histogram only knows about the data it was shown — on a column that only ever grows, every row added since is sitting in territory the optimizer has to guess about blind.


