Stale Statistics and Fragmentation Thresholds — A Production Case Study in Query Plan Instability

A weekly index maintenance job that had run without complaint for years turned out to be the quiet cause of measurable query plan instability. Statistics on high-churn tables drifted past 200% modified between runs. Query Store data pulled directly from production confirmed the consequence: the same query, same parameters, running in under a millisecond under one plan and over nine seconds under another, with some queries generating up to ten distinct execution plans inside a single week.

This article documents that investigation end to end: how statistics went stale despite an existing maintenance job, how that staleness showed up as confirmed plan instability in Query Store, and the maintenance redesign that followed. It also tackles a question that comes up in almost every conversation about index maintenance and refuses to have a single clean answer: what fragmentation thresholds actually make sense on SSD and NVMe storage. That section is deliberately marked throughout as practitioner-recommended guidance, not confirmed Microsoft policy, because that distinction turned out to matter.

1The Incident: Confirmed Plan Instability

The starting complaint was ordinary: intermittent slowness on a handful of reports, no obvious pattern. Query Store data pulled directly from the production instance turned “intermittent” into something measurable.

The starkest single line in that table is the query with only two plans and a 9,069 ms swing. Same query, same parameters, one plan finishes before a network round trip would even register and the other takes nine full seconds. That is not application variability; that is the query optimizer choosing between two fundamentally different strategies because the information it based the decision on had changed underneath it.

A second database in the same environment showed a query joining three tables generate three separate plan variants under SQL Server 2022’s Parameter Sensitive Plan (PSP) optimization, evidenced by an OPTION (PLAN PER VALUE...) hint visible in the captured query text. Despite PSP actively trying to give different parameter ranges their own tailored plan, total duration still ranged from roughly 1.5 seconds to over 502 seconds across the three variants combined. PSP optimization is designed to reduce exactly this kind of instability by not forcing one plan onto every parameter value; it cannot compensate for the underlying cardinality estimates being wrong in the first place.

A third database showed a monitoring query using GROUP BY against a table confirmed at just over 21 million rows generate four plans with a 358-second variance. This one is worth pausing on: it is not a customer-facing query, it is internal tooling, and it was still expensive enough in its worst plan to matter.

2Root Cause: How Statistics Go Stale Despite a Running Job

The maintenance job in question was not missing. It ran every week, reliably, and updated statistics as part of its work. The problem was the seven-day gap between runs on tables that did not have seven days to spare.

A statistics health check run on high-churn tables found several sitting at a “critical” tier (defined here as more than 10% of rows modified for tables at 1 million rows or larger, or more than 20% for smaller tables) well past that threshold:

Two of the rows above show staleness accumulating in a single day or less. On a table with meaningful write volume, a once-a-week check is not measuring drift, it is measuring whatever has piled up since the last time anyone looked. That gap is exactly what the “min 0.03 ms, max 9,069 ms” query above is explained by: statistics current enough for a good plan early in the week, badly stale by the time the next run was due.

3The NORECOMPUTE Trap

One detail is worth flagging separately because it is easy to miss and explains staleness that persists even when a job runs on schedule and reports success. AUTO_UPDATE_STATISTICS is supposed to trigger an automatic refresh independent of any scheduled job, whenever enough rows change relative to table size. For a table in the tens of thousands of rows, that threshold is a few thousand modifications, not hundreds of thousands. If a statistic sits stale for days despite genuinely heavy write volume on a modestly sized table, automatic update firing on its own is the expected behavior, and its absence is a signal worth chasing.

The most common reason it does not fire: STATISTICS_NORECOMPUTE was set to ON for that specific statistic, most often as a side effect of an index rebuild executed with that option, or a manual UPDATE STATISTICS ... WITH NORECOMPUTE. This disables future automatic updates for that one statistic, permanently, until someone explicitly re-enables it. Critically, sp_updatestats preserves whatever NORECOMPUTE setting is already present per statistic, so a scheduled job built around it will silently keep skipping an affected statistic forever, reporting success the entire time.

-- Check whether NORECOMPUTE is silently blocking auto-update on any statistic
SELECT
    t.name                          AS TableName,
    s.name                          AS StatName,
    s.no_recompute                  AS NoRecomputeSet,   -- 1 = auto-update disabled for this stat
    sp.last_updated,
    sp.modification_counter,
    sp.rows
FROM sys.stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.no_recompute = 1
  AND sp.modification_counter > 0
ORDER BY sp.modification_counter DESC;

If this returns rows, the fix is not a more frequent schedule. It is a one-time UPDATE STATISTICS <table> <stat> WITH FULLSCAN (without NORECOMPUTE) for each affected statistic, or ALTER INDEX <name> SET (STATISTICS_NORECOMPUTE = OFF) for index-backed statistics, which applies immediately without a rebuild. That restores the engine’s own continuous auto-update behavior, which is more reliable than any scheduled job for catching drift between runs.

4The Fragmentation Threshold Question: Spindle vs. SSD

The existing maintenance job used a 30% reorganize threshold and a 50% rebuild threshold. Those numbers trace back to Microsoft’s original published guidance of 5% and 30%, and that origin matters for what comes next.

The case for raising the thresholds on SSD

The physical cost that made fragmentation expensive on spinning disk was seek time: a mechanical read head physically moving to a non-contiguous location, at a cost of several milliseconds per seek, which adds up quickly across a large scattered scan. Flash storage has no read head and no seek time; random access and sequential access cost approximately the same. Independent empirical testing on this specific question found that on SSD, logical fragmentation percentage alone showed no measurable query performance impact, only a storage size difference. That finding has been corroborated by multiple independent practitioners over time.

The case for not dismissing fragmentation entirely on SSD

The counterargument is not that the SSD research above is wrong, but that it addresses only one half of what “fragmentation” commonly refers to. Fragmentation also produces low page density: pages left with more free space than necessary due to page splits, meaning more total pages must be read and cached to hold the same amount of data. That cost is a buffer pool and cache-efficiency cost, not a disk-seek cost, and it does not go away on flash storage. Rebuilding a fragmented index can meaningfully shrink its page count and improve cache hit ratio independent of storage medium.

What this specific environment changed to

The higher numbers were not accepted on faith. They were deployed first to a lower, production-scale test environment, measured against a confirmed baseline runtime, and only carried forward to production after that comparison. That sequencing, not the specific numbers, is the part of this section worth treating as settled practice.

5The Maintenance Redesign

Closing the statistics gap meant more than changing two percentages. The full redesign addressed frequency, storage placement, and blast radius together.

SORT_IN_TEMPDB is a documented, supported option on both CREATE INDEX and ALTER INDEX ... REBUILD. It moves the intermediate sort operation used to build the new index structure into tempdb rather than the same filegroup as the index itself, which is a legitimate lever specifically when tempdb sits on faster storage than the data files, as was confirmed to be the case here.

Deployment discipline

Every parameter change was tested in a lower environment before touching production, with an explicit before-and-after runtime comparison rather than an assumption that the new settings would simply be faster. The production job’s confirmed runtime, and the test environment’s confirmed runtime under the current settings, both became the baselines the new settings were measured against. Nothing was deployed to production without a documented comparison and change approval.

6Diagnostic Scripts

Statistics staleness by tiered threshold

SELECT
    t.name                                                        AS TableName,
    s.name                                                        AS StatName,
    sp.last_updated,
    sp.rows,
    sp.modification_counter,
    CASE WHEN sp.rows > 0
         THEN CAST(sp.modification_counter AS FLOAT) / sp.rows * 100
         ELSE 0 END                                                AS PctModified,
    CASE
        WHEN sp.rows >= 1000000 AND sp.modification_counter > sp.rows * 0.10 THEN 'CRITICAL'
        WHEN sp.rows <  1000000 AND sp.modification_counter > sp.rows * 0.20 THEN 'CRITICAL'
        ELSE 'OK'
    END                                                             AS Status
FROM sys.stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE sp.rows > 0
ORDER BY PctModified DESC;

Fragmentation with storage-aware thresholds

DECLARE @IsFlashStorage BIT = 1; -- confirm actual storage type before setting this

SELECT
    OBJECT_NAME(ips.object_id)         AS TableName,
    i.name                              AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    CASE
        WHEN @IsFlashStorage = 1 AND ips.avg_fragmentation_in_percent >= 80 THEN 'REBUILD'
        WHEN @IsFlashStorage = 1 AND ips.avg_fragmentation_in_percent >= 50 THEN 'REORGANIZE'
        WHEN @IsFlashStorage = 0 AND ips.avg_fragmentation_in_percent >= 30 THEN 'REBUILD'
        WHEN @IsFlashStorage = 0 AND ips.avg_fragmentation_in_percent >= 5  THEN 'REORGANIZE'
        ELSE 'NO ACTION'
    END                                  AS RecommendedAction
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 1000  -- ignore small indexes; fragmentation on them is often uncontrollable
ORDER BY ips.avg_fragmentation_in_percent DESC;

7Key Takeaways

• A weekly maintenance job is not automatically sufficient just because it runs successfully every time. On high-churn tables, statistics can drift past critical thresholds in a single day, and Query Store can directly confirm the resulting plan instability with real duration variance.
• If staleness persists despite AUTO_UPDATE_STATISTICS being enabled, check sys.stats.no_recompute before assuming the job needs to run more often. NORECOMPUTE silently disables the engine’s own continuous auto-update for a specific statistic, and sp_updatestats will keep skipping it indefinitely without any error.
• Microsoft’s original 5%/30% fragmentation thresholds were openly acknowledged by their author as a starting point, not a benchmarked standard, and were built for spinning-disk seek-time costs that do not exist on flash storage.
• Raising fragmentation thresholds for SSD and NVMe is well-supported, widely practiced guidance, but it is practitioner consensus, not a Microsoft-published standard. No current Microsoft Learn documentation specifies SSD-specific numbers.
• Low page density, a side effect of fragmentation, still carries a real cache-efficiency cost independent of storage medium, which is why “SSD means fragmentation does not matter at all” is an overstatement of the underlying research.
• Whatever thresholds are chosen, validate them with a measured before-and-after comparison in a representative environment before deploying to production. That sequencing matters more than the specific numbers.

The technical information in this article was verified against Microsoft documentation at the time of publication. SQL Server features, cloud service capabilities, licensing terms, and configuration requirements can change between versions and cumulative updates. Always validate implementation details against current Microsoft Learn documentation before deploying to production. Guidance on fragmentation thresholds for SSD and NVMe storage reflects practitioner consensus, not confirmed Microsoft policy, and is explicitly labeled as such throughout this article.

References

• Microsoft Docs: ALTER INDEX (Transact-SQL)
• Microsoft Docs: UPDATE STATISTICS (Transact-SQL)
• Microsoft Docs: Statistics (SQL Server)
• Microsoft Docs: sp_updatestats (Transact-SQL)
• Microsoft Docs: Reorganize and Rebuild Indexes (archived, SQL Server 2014; original source of the 5%/30% thresholds, not updated for current versions)
• Community and Industry Sources: Paul S. Randal, “Where do the Books Online index fragmentation thresholds come from?” (SQLskills)
• Community and Industry Sources: Jonathan Kehayias, “Does Index Fragmentation Matter with SSDs?” (SQLskills)
• SQLYARD: SQL Server Index Fragmentation: Myths, Measurement, and Maintenance
• SQLYARD: A Beginner-to-Advanced Guide to Query Store in SQL Server
• SQLYARD: When SQL Server Statistics Stop Auto-Updating: Detecting and Fixing the Silent Failure
• SQLYARD: SQL Server Cost Threshold for Parallelism: The Right Setting