When SQL Server Statistics Stop Auto-Updating: Detecting and Fixing the Silent Failure

---

SQL Server’s automatic statistics update feature is designed to keep the query optimizer’s cardinality estimates accurate as data changes. When it works, it is invisible. When it silently fails, the optimizer continues making execution plan decisions based on data distribution that may be weeks or months out of date, producing plans that are increasingly wrong as the data diverges from the last known histogram.

The dangerous part is that the failure is silent. No alert fires. No error appears in the log. The database continues running, queries continue executing, and performance slowly degrades until someone investigates why a query that ran in two seconds last month now takes forty-five. By that point statistics may be 100 days old on a table that has seen 70 million row modifications, sitting at 150 times the auto-update threshold with no update ever triggered.

This article covers the specific reasons auto-update statistics fails, how to detect each root cause with scripts, the critical bug in a widely-used detection query pattern, and a complete investigation and remediation workshop.

1 How Auto-Update Statistics Is Supposed to Work Beginner

SQL Server tracks data modifications on the leading column of each statistics object through an internal modification counter. According to Microsoft documentation, statistics are automatically updated when the query optimizer accesses them and they are considered out of date. A statistics object is considered out of date when the number of data changes since the last update exceeds a threshold.

The threshold formula depends on the SQL Server version and database compatibility level:

SQL Server Version / Compat Level	Threshold Formula	Example: 2.3M Row Table
SQL Server 2014 and earlier, or compat level below 130	500 + (20% of rows at last update)	500 + (20% x 2,313,946) = 463,289 modifications required
SQL Server 2016 and later with compat level 130 or higher	MIN(500 + 0.20 x n, SQRT(1,000 x n))	MIN(463,289; 48,104) = 48,104 modifications required. Updates happen far more frequently.

The auto-update fires when a query accesses the statistics object and the modification counter exceeds the threshold. SQL Server does not proactively push updates on a schedule. The trigger is a query accessing stale statistics at compile time. If no query accesses a particular statistics object, it never auto-updates regardless of how many modifications have occurred.

2 The Five Reasons Auto-Update Silently Fails Beginner

When statistics are well past the auto-update threshold but have not been updated, one of five conditions is responsible. All are verifiable through DMVs and system catalog queries.

Root Cause	How It Happens	How to Detect
NORECOMPUTE set on statistics object	The most common silent killer. UPDATE STATISTICS or ALTER INDEX REBUILD run with NORECOMPUTE = ON permanently disables auto-update for that specific statistics object. According to Microsoft documentation, sp_updatestats also preserves the NORECOMPUTE setting, meaning routine maintenance leaves the flag in place.	Script in Section 6
AUTO_UPDATE_STATISTICS OFF at database level	The database-level setting is switched off, disabling auto-update for all statistics in the database. Every query runs on potentially stale statistics.	Script in Section 4
AUTO_UPDATE_STATISTICS_ASYNC ON with blocked background thread	Async mode queues the update to run in the background. If the background thread is blocked, never scheduled, or the server is overwhelmed, the queue entry sits indefinitely. Queries do not wait for the update and continue using stale statistics.	Script in Section 4 plus sys.dm_exec_requests
Wrong compatibility level using old threshold	A database on compatibility level below 130 uses the old 20% threshold. For large tables this threshold may never be reached during normal auto-update cycles, especially if queries do not access the statistics frequently enough to trigger the check.	Script in Section 3
Statistics object not accessed by any query	Auto-update fires only when a query accesses the statistics at compile time. If no query uses a particular statistics object, it never triggers regardless of modification count. This is expected behavior, not a failure.	Scripts 3 and 4 from the companion article (plan cache and Query Store)

3 The Compatibility Level Threshold Trap Intermediate

The compatibility level trap is the least obvious failure mode. A database running SQL Server 2016 or later but at compatibility level 120 (SQL Server 2014 equivalent) or lower uses the old 20% threshold formula. For large tables this creates a situation where the threshold is technically correct but the auto-update still fails to fire in practice.

For a table with 2,313,946 rows under the old formula: the threshold is 500 + (20% x 2,313,946) = 463,289 modifications. If the table receives steady write activity throughout the day, the modification counter may accumulate over days or weeks. However auto-update only fires when a query accesses those statistics at compile time and finds them over threshold. If the plan for queries on that table is already in cache, no recompile occurs, no statistics check happens, and the counter continues growing unchecked.

The result is statistics that are technically past threshold but never trigger auto-update because no compile-time check evaluates them. The fix is two-fold: move the database to compatibility level 130 or higher to use the improved threshold formula, and implement scheduled statistics maintenance that runs independently of query-triggered auto-updates.

-- Check compatibility level and auto-update settings for all databases
SELECT
    name,
    compatibility_level,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on,
    is_auto_create_stats_on,
    -- Flag databases using old threshold formula
    CASE WHEN compatibility_level < 130
        THEN 'OLD THRESHOLD (20%) - Consider upgrading to compat 130+'
        ELSE 'MODERN THRESHOLD (SQRT formula)'
    END                             AS ThresholdFormula
FROM sys.databases
WHERE database_id > 4              -- user databases only
ORDER BY name;

4 Step 1: Confirm Database-Level Settings Beginner

Before investigating individual statistics objects, verify the database-level configuration. A single OFF setting here explains every statistics problem in the database at once.

-- Database-level statistics settings
-- Run in the context of the database being investigated
SELECT
    name                                    AS DatabaseName,
    compatibility_level,
    is_auto_update_stats_on                 AS AutoUpdateStatsON,
    is_auto_update_stats_async_on           AS AutoUpdateStatsAsyncON,
    is_auto_create_stats_on                 AS AutoCreateStatsON,
    -- Critical: if AUTO_UPDATE_STATISTICS is OFF, no stats in this
    -- database will ever auto-update regardless of modification count
    CASE WHEN is_auto_update_stats_on = 0
        THEN 'CRITICAL: Auto-update disabled for entire database'
        WHEN is_auto_update_stats_async_on = 1
        THEN 'ASYNC mode: updates are queued, may be delayed'
        ELSE 'OK'
    END                                     AS Status
FROM sys.databases
WHERE name = DB_NAME();

-- If AUTO_UPDATE_STATISTICS is OFF, enable it:
-- ALTER DATABASE [YourDatabase] SET AUTO_UPDATE_STATISTICS ON;

-- If ASYNC is causing delays and is not needed:
-- ALTER DATABASE [YourDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;

5 Step 2: The Golden Statistics Health Check Query Intermediate

The query below is the core detection script. It shows all statistics objects with meaningful modification activity, calculates the percentage of drift from the last known histogram, and assigns a status based on severity.

-- Golden Statistics Health Check
-- Detects statistics with significant modification drift
-- Verified against Microsoft Learn documentation
-- Run in the context of the database to investigate

SELECT
    DB_NAME()                                               AS DatabaseName,
    s.name + '.' + o.name                                  AS TableName,
    st.name                                                 AS StatName,
    st.no_recompute                                         AS NoRecomputeEnabled,  -- TRUE = auto-update DISABLED
    st.auto_created                                         AS IsAutoCreated,
    sp.last_updated,
    DATEDIFF(DAY, sp.last_updated, GETDATE())               AS DaysSinceUpdate,
    sp.rows                                                 AS TableRows,
    sp.rows_sampled,
    CAST(100.0 * sp.rows_sampled
         / NULLIF(sp.rows, 0) AS DECIMAL(6,2))             AS SamplePct,
    sp.modification_counter                                 AS RowsModified,
    CASE WHEN sp.rows > 0
         THEN CAST(sp.modification_counter * 100.0
                   / sp.rows AS DECIMAL(10,2))
         ELSE 0
    END                                                     AS PctModified,
    -- Auto-update threshold (modern formula: compat 130+)
    CAST(SQRT(1000.0 * sp.rows) AS BIGINT)                 AS ModernThreshold,
    -- Auto-update threshold (old formula: compat < 130)
    CAST(500 + (0.20 * sp.rows) AS BIGINT)                 AS OldThreshold,
    -- How many times over the old threshold are we?
    CASE WHEN (500 + (0.20 * sp.rows)) > 0
         THEN CAST(sp.modification_counter
                   / (500 + (0.20 * sp.rows)) AS DECIMAL(10,1))
         ELSE NULL
    END                                                     AS TimesOverOldThreshold,
    CASE
        WHEN sp.modification_counter * 100.0
             / NULLIF(sp.rows, 0) > 100 THEN 'CRITICAL'
        WHEN sp.modification_counter * 100.0
             / NULLIF(sp.rows, 0) > 20  THEN 'HIGH'
        WHEN DATEDIFF(DAY, sp.last_updated, GETDATE()) > 30
             AND sp.modification_counter > 0              THEN 'STALE'
        ELSE 'OK'
    END                                                     AS Status,
    -- Generate the update statement
    CASE
        WHEN sp.modification_counter * 100.0 / NULLIF(sp.rows, 0) > 20
            THEN 'UPDATE STATISTICS '
                 + QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
                 + ' ' + QUOTENAME(st.name)
                 + CASE WHEN sp.rows < 1000000
                        THEN ' WITH FULLSCAN;'
                        ELSE ' WITH SAMPLE 30 PERCENT;'
                   END
        ELSE NULL
    END                                                     AS UpdateStatement
FROM sys.stats                                              st
JOIN sys.objects                                            o
    ON st.object_id = o.object_id
JOIN sys.schemas                                            s
    ON o.schema_id = s.schema_id
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp
WHERE o.type = 'U'                  -- user tables only (covers sys exclusion)
  AND sp.modification_counter > 0
  AND sp.rows > 0
ORDER BY sp.modification_counter DESC;

6 Step 3: Detect NORECOMPUTE on Statistics Objects Intermediate

The query above shows no_recompute per statistics object. This script provides a table-level view showing which tables have any statistics objects with NORECOMPUTE enabled, and whether the flag is on index statistics, auto-created column statistics, or user-created statistics.

-- Detect all statistics objects with NORECOMPUTE enabled
-- These will NEVER auto-update regardless of modification count
-- or database-level AUTO_UPDATE_STATISTICS setting

SELECT
    SCHEMA_NAME(o.schema_id)                AS SchemaName,
    o.name                                  AS TableName,
    st.name                                 AS StatsName,
    st.no_recompute                         AS NoRecomputeEnabled,
    st.auto_created                         AS AutoCreated,
    st.user_created                         AS UserCreated,
    -- Determine stats type
    CASE
        WHEN i.name IS NOT NULL THEN 'Index statistics: ' + i.name
        WHEN st.auto_created = 1 THEN 'Auto-created column statistics'
        ELSE 'User-created statistics'
    END                                     AS StatsType,
    sp.last_updated,
    DATEDIFF(DAY, sp.last_updated, GETDATE()) AS DaysSinceUpdate,
    sp.modification_counter                 AS Modifications,
    -- Script to CLEAR NORECOMPUTE (re-enable auto-update)
    -- WARNING: review each one before running
    'UPDATE STATISTICS '
        + QUOTENAME(SCHEMA_NAME(o.schema_id))
        + '.' + QUOTENAME(o.name)
        + ' ' + QUOTENAME(st.name)
        + ' WITH FULLSCAN;'
        + '  -- Clears NORECOMPUTE, re-enables auto-update'
                                            AS FixScript
FROM sys.stats                              st
JOIN sys.objects                            o
    ON st.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp
LEFT JOIN sys.indexes                       i
    ON st.object_id = i.object_id
    AND st.name     = i.name               -- match stats to index by name
WHERE o.is_ms_shipped = 0
  AND o.type          = 'U'
  AND st.no_recompute = 1                  -- ONLY show stats with NORECOMPUTE
ORDER BY
    DaysSinceUpdate DESC,
    sp.modification_counter DESC;

-- Count of affected objects by table for quick triage
SELECT
    SCHEMA_NAME(o.schema_id)        AS SchemaName,
    o.name                          AS TableName,
    COUNT(*)                        AS StatsWithNorecompute
FROM sys.stats                      st
JOIN sys.objects                    o ON st.object_id = o.object_id
WHERE o.is_ms_shipped = 0
  AND o.type          = 'U'
  AND st.no_recompute = 1
GROUP BY SCHEMA_NAME(o.schema_id), o.name
ORDER BY StatsWithNorecompute DESC;

7 Step 4: Prove the Failure with Mathematical Threshold Analysis Intermediate

When presenting the case for why statistics maintenance is failing, concrete numbers are more persuasive than general descriptions. This script produces the mathematical proof showing how far over threshold each statistics object has drifted and how many times over the expected trigger point current modifications represent.

-- Mathematical threshold analysis
-- Shows exactly how far each statistics object has exceeded its trigger threshold
-- Useful for presenting evidence of failure to management or development teams

SELECT
    SCHEMA_NAME(o.schema_id)                                AS SchemaName,
    o.name                                                  AS TableName,
    st.name                                                 AS StatsName,
    st.no_recompute                                         AS AutoUpdateDisabled,
    sp.rows                                                 AS TotalRows,
    sp.last_updated                                         AS LastUpdated,
    DATEDIFF(DAY, sp.last_updated, GETDATE())               AS DaysSinceUpdate,
    sp.modification_counter                                 AS ActualModifications,

    -- Old threshold (compat < 130): 500 + 20% of rows
    CAST(500 + (0.20 * sp.rows) AS BIGINT)                 AS OldThreshold,

    -- Modern threshold (compat 130+): SQRT formula
    CAST(SQRT(1000.0 * sp.rows) AS BIGINT)                 AS ModernThreshold,

    -- Times over old threshold (most visible for large tables)
    CASE WHEN (500 + (0.20 * sp.rows)) > 0
         THEN CAST(sp.modification_counter
                   / NULLIF(500 + (0.20 * sp.rows), 0) AS DECIMAL(10,1))
         ELSE NULL
    END                                                     AS TimesOverOldThreshold,

    -- Times over modern threshold
    CASE WHEN SQRT(1000.0 * sp.rows) > 0
         THEN CAST(sp.modification_counter
                   / NULLIF(CAST(SQRT(1000.0 * sp.rows) AS BIGINT), 0) AS DECIMAL(10,1))
         ELSE NULL
    END                                                     AS TimesOverModernThreshold,

    -- Modification percentage
    CAST(sp.modification_counter * 100.0
         / NULLIF(sp.rows, 0) AS DECIMAL(10,2))            AS ModificationPct,

    -- Verdict
    CASE
        WHEN st.no_recompute = 1
            THEN 'AUTO-UPDATE PERMANENTLY DISABLED (NORECOMPUTE)'
        WHEN sp.modification_counter * 100.0
             / NULLIF(sp.rows, 0) > 100
            THEN 'CRITICAL: Over 100% modified since last update'
        WHEN sp.modification_counter * 100.0
             / NULLIF(sp.rows, 0) > 20
            THEN 'HIGH: Over 20% modified since last update'
        ELSE 'MONITOR'
    END                                                     AS Verdict

FROM sys.stats                                              st
JOIN sys.objects                                            o
    ON st.object_id = o.object_id
JOIN sys.schemas                                            s
    ON o.schema_id  = s.schema_id
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp
WHERE o.is_ms_shipped = 0
  AND o.type = 'U'
  AND sp.rows > 0
  AND sp.modification_counter > 0
  AND (
        sp.modification_counter * 100.0 / NULLIF(sp.rows, 0) > 20
        OR st.no_recompute = 1
      )
ORDER BY TimesOverOldThreshold DESC NULLS LAST,
         sp.modification_counter DESC;

8 Fixing Each Root Cause Intermediate

Fix 1: NORECOMPUTE set on statistics objects

-- Clear NORECOMPUTE on a specific statistics object
-- This re-enables auto-update for that statistics object
-- Running WITHOUT NORECOMPUTE in the WITH clause is what clears the flag
-- (Microsoft documentation: running UPDATE STATISTICS without NORECOMPUTE
-- re-enables the AUTO_UPDATE_STATISTICS behavior for that statistics object)

UPDATE STATISTICS [SchemaName].[TableName] [StatsObjectName]
WITH FULLSCAN;
-- No NORECOMPUTE in the WITH clause = flag is cleared, auto-update re-enabled

-- If NORECOMPUTE is on many statistics objects for a table,
-- update all statistics on that table at once:
UPDATE STATISTICS [SchemaName].[TableName] WITH FULLSCAN;

-- Verify the flag is cleared after running:
SELECT name, no_recompute
FROM sys.stats
WHERE object_id = OBJECT_ID('[SchemaName].[TableName]')
ORDER BY name;

Fix 2: AUTO_UPDATE_STATISTICS disabled at database level

-- Re-enable auto-update statistics at the database level
ALTER DATABASE [YourDatabase] SET AUTO_UPDATE_STATISTICS ON;

-- Verify:
SELECT name, is_auto_update_stats_on
FROM sys.databases
WHERE name = '[YourDatabase]';

Fix 3: Compatibility level using old threshold

-- Before changing compatibility level: test application queries first
-- Changing compatibility level affects the query optimizer's behaviour
-- across the entire database. Test in non-production before applying.

-- Check current level
SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();

-- Move to compatibility level 130 (SQL Server 2016 baseline)
-- to enable the modern SQRT statistics threshold formula
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 130;

-- Recommended: move to the current supported level for the SQL Server version
-- SQL Server 2022 = 160
-- SQL Server 2019 = 150
-- SQL Server 2017 = 140
-- SQL Server 2016 = 130

9 Ola Hallengren: The Right Configuration Intermediate

For environments running Ola Hallengren’s IndexOptimize, the critical parameter for statistics maintenance is @UpdateStatistics. The default configuration of IndexOptimize does not update column statistics separately from index rebuilds. Index rebuilds update the statistics on the index key columns only. Column statistics created by the optimizer on non-index columns require a separate update that only happens when @UpdateStatistics = 'ALL' is specified.

-- Recommended IndexOptimize configuration for comprehensive statistics maintenance
-- This updates both index statistics and all column statistics
-- @OnlyModifiedStatistics = 'Y' limits updates to stats with modification_counter > 0
-- reducing runtime by skipping stats on tables with no data changes

EXECUTE dbo.IndexOptimize
    @Databases             = 'USER_DATABASES',
    @FragmentationLow      = NULL,          -- skip low fragmentation index work
    @FragmentationMedium   = 'INDEX_REORGANIZE',
    @FragmentationHigh     = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @UpdateStatistics      = 'ALL',         -- critical: covers column stats too
    @OnlyModifiedStatistics = 'Y',          -- only update where modification_counter > 0
    @StatisticsSample      = 30,            -- 30% sample for large tables
    @StatisticsScanType    = 'DEFAULT';     -- FULLSCAN for tables < 1GB, SAMPLE for larger

-- For critical tables where statistics quality is essential,
-- use FULLSCAN regardless of table size:
EXECUTE dbo.IndexOptimize
    @Databases             = 'USER_DATABASES',
    @FragmentationLow      = NULL,
    @UpdateStatistics      = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @StatisticsSample      = 100,           -- FULLSCAN for all tables
    @StatisticsScanType    = 'DEFAULT';

10 Workshop: Full Investigation from Detection to Fix Advanced

This workshop walks through the complete investigation cycle. Run it against a database where statistics behavior is uncertain. Each step builds on the previous to narrow down the root cause before executing any fix.

1. Run Step 1 (Section 4) to confirm database-level settings. If is_auto_update_stats_on = 0, stop here: this is the root cause for the entire database. Enable it and recheck. If ASYNC is ON, note this as a contributing factor.
2. Run Step 2 (Section 5) to get the full statistics health picture. Sort by modification_counter DESC. Identify which tables have CRITICAL or HIGH status. Note the NoRecomputeEnabled column value for each.
3. For any table showing CRITICAL or HIGH with NoRecomputeEnabled = 1, run Step 3 (Section 6) to get the full list of statistics objects with NORECOMPUTE. This is now a confirmed diagnosis, not a theory. The statistics will never auto-update until the flag is cleared.
4. Run Step 4 (Section 7) to calculate the mathematical threshold analysis. Record how many times over threshold the worst offenders are, and how many days since last update. This produces the evidence needed to justify immediate maintenance.
5. Execute the fix from Section 8 appropriate to the root cause identified. For NORECOMPUTE: run UPDATE STATISTICS without NORECOMPUTE. For database-level OFF: run ALTER DATABASE. For compatibility level: plan and test before applying.
6. Re-run Step 2 immediately after fixing. Confirm modification_counter reset to 0 after the statistics update. Confirm no_recompute is now 0 for the fixed statistics objects.
7. Update the Ola Hallengren job to include @UpdateStatistics = 'ALL' so column statistics are covered in every future maintenance run.
8. Schedule the golden health check query from Section 5 as a SQL Agent job that runs weekly and emails results when any statistics object shows CRITICAL status. This provides the ongoing visibility that prevents 100-day-old statistics from going undetected again.

References

• Microsoft Docs: Statistics (SQL Server)
• Microsoft Docs: UPDATE STATISTICS (Transact-SQL) - NORECOMPUTE behavior
• Microsoft Docs: sp_updatestats - NORECOMPUTE preservation behavior
• Microsoft Docs: sys.dm_db_stats_properties
• Microsoft Blog: Changes to Automatic Update Statistics in SQL Server - Trace Flag 2371 and the SQRT threshold
• Microsoft SQL Server Support Blog: NORECOMPUTE Can Lead to SQL Server Performance Degradation
• Microsoft Docs: ALTER DATABASE SET Options - AUTO_UPDATE_STATISTICS
• Ola Hallengren: SQL Server Index and Statistics Maintenance - @UpdateStatistics parameter
• SQLYARD: SQL Server Index Fragmentation and Statistics: Detection, Alerting, and Maintenance Without Blind Assumptions