Stale Statistics: Detecting and Updating Them

The load finished, but the next query can still see the earlier distribution. Investigating stale statistics connects that change to the specific statistics and plans that influence the workload.

Trace Stale Statistics Behind a Bad Estimate

Statistics describe distributions used by the optimizer, not a continuously maintained count of every business value. A substantial change can leave those distributions unsuitable for a query. The effect depends on the relevant columns, ranges, skew, and the plan's sensitivity to its estimates.

Do not equate stale statistics with old timestamps alone. A statistic updated months ago can remain useful on unchanged data, while a recent statistic can inadequately represent a newly changed range. I begin with the plan's estimates and the known data change rather than refreshing everything because a timestamp looks old.

Identify the table, predicate, join, and important operators. Compare estimated and actual rows from an approved execution. Missing statistics, sampling, parameter sensitivity, or a new ascending range can require different responses. An update command cannot infer which business change the query is trying to describe.

Inspect Update Time, Sample, and Modifications

The statistics-properties function exposes the last update time, rows, sampled rows, and modification counter. Use OUTER APPLY so an unavailable properties row does not remove the statistic from the inventory. Interpret NULL properties as needing investigation rather than a successful freshness check.

SELECT s.name AS SchemaName,t.name AS TableName,st.name AS StatisticName,
       st.stats_id,st.auto_created,st.user_created,st.has_filter,
       p.last_updated,p.rows,p.rows_sampled,p.modification_counter
FROM sys.stats AS st
JOIN sys.tables AS t ON t.object_id=st.object_id
JOIN sys.schemas AS s ON s.schema_id=t.schema_id
OUTER APPLY sys.dm_db_stats_properties(st.object_id,st.stats_id) AS p
WHERE t.is_ms_shipped=0
ORDER BY p.modification_counter DESC,s.name,t.name,st.stats_id;

The modification counter has documented statistics-column scope; it is not a complete audit of every changed value in the table. Filtered statistics describe a specific subset. Review that scope and the statistic's leading column before turning a counter into a maintenance decision. Partitioned and incremental statistics need their relevant partition-level review as well.

Record the capture time, database, and query identity with the candidate. A maintenance job or automatic refresh can change the properties before the proposed operation begins. Recheck the target when the delay is material. A snapshot is useful evidence about one state, not a permanent freshness verdict.

Understand the Automatic Update Trigger

Automatic updating evaluates modification thresholds when relevant optimizer activity needs the statistics. It does not refresh every statistic immediately after each write. Inspect the database settings and compatibility level before interpreting the behavior.

SELECT name,compatibility_level,is_auto_create_stats_on,
       is_auto_update_stats_on,is_auto_update_stats_async_on
FROM sys.databases WHERE database_id=DB_ID();

For larger permanent-table statistics, older behavior used a threshold based on 500 plus twenty percent of the recorded row count. The dynamic threshold, the default from compatibility level 130, reduces the threshold for large tables. The documented formula uses the smaller of that older threshold and the square root of 1,000 times the row count in the applicable case.

That description is not a universal threshold for every temporary table, tiny table, filtered statistic, or special object. Check the documented rules for the exact scope. Also distinguish synchronous and asynchronous updating. An asynchronous compile can use existing statistics while refresh proceeds, which matters for a report immediately following a large load.

Refresh Stale Statistics Deliberately

Choose a targeted refresh when a particular statistic influences the problem. The following example assumes the named lab table and index statistic exist. FULLSCAN reads the complete relevant population; a sample reduces collection work with a different representation tradeoff.

UPDATE STATISTICS dbo.StatsTarget(IX_StatsTarget_Customer) WITH FULLSCAN;
UPDATE STATISTICS dbo.StatsTarget(IX_StatsTarget_Customer) WITH SAMPLE 25 PERCENT;

These are alternative experiments, not a recommendation to run the sampled update immediately after every full scan. Compare the resulting representation and plan under the accepted workload. An index rebuild can refresh its related statistics, but it does not automatically replace every separate column-statistics maintenance need.

Review CPU, I/O, locks, duration, and compilation effects in the maintenance window. FULLSCAN is a collection method, not a guarantee of one ideal plan. Query shape and parameter behavior still matter. I keep the before-and-after estimates with the update result so a completed command is not mistaken for a resolved performance problem.

Schedule a Narrow Nightly Policy for Stale Statistics

A nightly procedure can select statistics with substantial modifications and record each attempt. The following lab policy uses explicit example thresholds, not SQL Server's automatic-update formula. Create a disposable database named StatsMaintenanceLab, install it there, and evaluate its candidate list before scheduling.

CREATE TABLE dbo.StatsMaintenanceLog
(
    AttemptID bigint IDENTITY PRIMARY KEY,
    SchemaName sysname,TableName sysname,StatisticName sysname,
    StartedUTC datetime2(0),FinishedUTC datetime2(0) NULL,
    Outcome nvarchar(20),Detail nvarchar(2000) NULL
);
GO
CREATE OR ALTER PROCEDURE dbo.RefreshChangedStats
AS
BEGIN
    SET NOCOUNT ON;
    IF @@TRANCOUNT<>0 THROW 50000,'Run statistics maintenance outside an existing transaction.',1;
    DECLARE @Schema sysname,@Table sysname,@Statistic sysname;
    DECLARE @Statement nvarchar(max),@AttemptID bigint;
    DECLARE StatsToRefresh CURSOR LOCAL FAST_FORWARD FOR
        SELECT s.name,t.name,st.name
        FROM sys.stats AS st
        JOIN sys.tables AS t ON t.object_id=st.object_id
        JOIN sys.schemas AS s ON s.schema_id=t.schema_id
        CROSS APPLY sys.dm_db_stats_properties(st.object_id,st.stats_id) AS p
        WHERE t.is_ms_shipped=0 AND t.name<>N'StatsMaintenanceLog'
          AND p.rows>0 AND p.modification_counter>=10000
          AND p.modification_counter>=CONVERT(decimal(28,4),p.rows)*0.10;
    OPEN StatsToRefresh;
    FETCH NEXT FROM StatsToRefresh INTO @Schema,@Table,@Statistic;
    WHILE @@FETCH_STATUS=0
    BEGIN
        INSERT dbo.StatsMaintenanceLog
            (SchemaName,TableName,StatisticName,StartedUTC,Outcome)
        VALUES(@Schema,@Table,@Statistic,SYSUTCDATETIME(),N'Running');
        SET @AttemptID=CONVERT(bigint,SCOPE_IDENTITY());
        SET @Statement=N'UPDATE STATISTICS '+QUOTENAME(@Schema)+N'.'+QUOTENAME(@Table)
            +N'('+QUOTENAME(@Statistic)+N') WITH RESAMPLE;';
        BEGIN TRY
            EXEC sys.sp_executesql @Statement;
            UPDATE dbo.StatsMaintenanceLog
            SET FinishedUTC=SYSUTCDATETIME(),Outcome=N'Completed'
            WHERE AttemptID=@AttemptID;
        END TRY
        BEGIN CATCH
            UPDATE dbo.StatsMaintenanceLog
            SET FinishedUTC=SYSUTCDATETIME(),Outcome=N'Failed',Detail=ERROR_MESSAGE()
            WHERE AttemptID=@AttemptID;
            CLOSE StatsToRefresh;
            DEALLOCATE StatsToRefresh;
            THROW;
        END CATCH;
        FETCH NEXT FROM StatsToRefresh INTO @Schema,@Table,@Statistic;
    END;
    CLOSE StatsToRefresh;
    DEALLOCATE StatsToRefresh;
END;
GO

RESAMPLE follows the existing sample rate and can be unsuitable when that rate produced a poor representation. Replace it with a reviewed per-statistic collection policy where needed. The example excludes zero-row properties from its automatic candidate list; newly loaded or unavailable statistics require separate review rather than being silently declared current.

The fractional threshold can miss a concentrated range change on a huge table. Keep an explicit important-statistic override policy for known post-load workloads. Limit concurrency to one maintenance runner and handle stale Running records if the host stops. The recorded command result describes maintenance completion, not the application's subsequent performance.

Attach the Procedure to an Agent Schedule

The example creates a daily one-o'clock job in the specified lab database. The procedure must already exist there, and the approved job owner needs the required rights. Configure failure notification and retention for the maintenance log before production adoption.

USE msdb;
GO
EXEC dbo.sp_add_job @job_name=N'Changed Statistics';
EXEC dbo.sp_add_jobstep @job_name=N'Changed Statistics',
    @step_name=N'Refresh reviewed candidates',@subsystem=N'TSQL',
    @database_name=N'StatsMaintenanceLab',
    @command=N'EXEC dbo.RefreshChangedStats;';
EXEC dbo.sp_add_schedule @schedule_name=N'Daily Statistics',
    @freq_type=4,@freq_interval=1,@active_start_time=10000;
EXEC dbo.sp_attach_schedule @job_name=N'Changed Statistics',
    @schedule_name=N'Daily Statistics';
EXEC dbo.sp_add_jobserver @job_name=N'Changed Statistics';
GO

Which report needs its refresh before that schedule runs? Align important maintenance with the data-load dependency rather than the convenience of midnight. A nightly policy is supplemental when automatic maintenance already serves ordinary traffic. Avoid overlapping it with expensive loads and other maintenance without a capacity review.

Validate the Query After the Refresh

Compare the relevant properties and actual plans after maintenance. Check estimates, reads, CPU, latency, and the accepted result contract. Preserve unsuccessful trials as well as the final accepted collection method. Repeatedly refreshing the same statistic without improving the plan is evidence to investigate another cause.

Stale statistics are a useful diagnosis only when their outdated representation explains the workload's estimates. Keep targeted maintenance, measured query behavior, and the known data-change schedule together. A timestamp that advances is one maintenance result, not a complete performance verdict.

Related reading on this blog: Find Oldest Updated Statistics: Outdated Statistics and Statistics Modification Counter: sys.dm_db_stats_properties.

A statistics update is not a performance guarantee, it is new distribution evidence that the workload still needs to validate.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.