How to Find Outdated Statistics? – Interview Question of the Week #081

Question: How do you find outdated statistics?

Answer: Inspect when a statistics object was last updated, its sampling and relevant modifications, then relate them to the query’s estimates. Its age in days alone is not a reliable stale-statistics test.

An attendee asked me during a performance-tuning webcast whether we could find all outdated statistics by their dates. A date is an easy field to sort; deciding whether the information misrepresents your data is the real task. Statistics can become stale, but an old date doesn’t automatically make them wrong, and a recent date doesn’t guarantee good estimates for your query.

Read the Statistics Properties

This inventory returns one row per statistics object, including index statistics, and avoids duplicate rows from joining every partition:

USE AdventureWorks2025;
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName,
       o.name AS TableName, s.name AS StatisticsName,
       COL_NAME(s.object_id, sc.column_id) AS LeadingColumn,
       p.last_updated, p.rows, p.rows_sampled, p.modification_counter,
       CAST(100.0 * p.modification_counter / NULLIF(p.rows,0)
            AS decimal(12,2)) AS ModificationPct,
       s.auto_created, s.user_created, s.no_recompute
FROM sys.stats AS s
JOIN sys.objects AS o ON o.object_id = s.object_id AND o.type = 'U'
JOIN sys.stats_columns AS sc ON sc.object_id = s.object_id
    AND sc.stats_id = s.stats_id AND sc.stats_column_id = 1
OUTER APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) AS p
ORDER BY SchemaName, TableName, StatisticsName;

last_updated records the statistics update, not an index’s last rebuild. rows and rows_sampled help interpret the snapshot and sampling. For disk-based tables, modification_counter concerns changes to the leading statistics column, not a count of distinct rows that changed exactly once. ModificationPct is an investigation aid, not a universal update threshold.

An empty or filtered-empty statistic can have NULL properties or no statistics blob. Lack of permissions can also limit visibility. Keep those cases visible and investigate them rather than pretending a missing date means “fresh.” Memory-optimized statistics have different counter behavior.

The Automatic Threshold

You’ll still see the rule “500 rows plus 20 percent” for larger tables. SQL Server 2016 and later use a dynamic threshold at compatibility level 130 or higher. It is the smaller of 500 + 0.20 × rows and the square root of 1000 × rows. These are engine triggers, not promises that every important distribution change waits for that percentage.

Auto-create and auto-update statistics are different settings. Creating missing statistics is not the same operation as refreshing an existing statistic. I usually want both enabled, but that doesn’t make investigation unnecessary. A few new high values, skew, filtered statistics or an important recent load can still deserve attention.

Choose the Update Based on Evidence

The database-wide command is:

EXEC sys.sp_updatestats;

That’s a maintenance operation, not part of the read-only inventory. A targeted UPDATE STATISTICS may be more appropriate when you have identified a specific object’s estimates or sampling problem. Updating statistics can change compilation and plans; it doesn’t guarantee faster execution.

Look at estimated versus actual rows where the bad choice begins, inspect the relevant statistic, and measure the query after a justified update. If the problem is an expression, correlation or a missing index, refreshing a histogram alone may leave the real cause untouched.

An old statistic is not automatically a wrong one, it is a candidate, and the query’s estimates tell you which ones matter.

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