The statistics date is old, but how much of the relevant data changed? The statistics modification counter adds that missing context. Read it beside the population and sample size before deciding whether a particular statistics object needs work.
Inspect Statistics Rather Than Guessing From Age
sys.dm_db_stats_properties returns properties for a statistics object in the current database. Pair it with sys.stats to identify the object and statistics name. Join the table and schema metadata too. The same table name in two schemas should never turn a diagnostic report into a guessing exercise.
I start with the most-modified statistics, then connect them to the affected query. A date alone is a weak reason to refresh everything. A large unchanged table can retain useful statistics. A recently updated table can gain a new concentration of values that changes estimates for the very next application query.
SELECT TOP (50) sch.name AS schema_name, t.name AS table_name,
s.name AS statistics_name, s.stats_id,
p.last_updated, p.rows, p.rows_sampled,
p.modification_counter
FROM sys.tables AS t
JOIN sys.schemas AS sch ON sch.schema_id = t.schema_id
JOIN sys.stats AS s ON s.object_id = t.object_id
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE t.is_ms_shipped = 0
ORDER BY p.modification_counter DESC;OUTER APPLY keeps the statistics metadata visible when the function returns no row. Check permissions and the validity of the object before explaining missing properties. The function requires the appropriate metadata access or statistics permissions. An empty result from a restricted account does not establish that statistics are healthy or absent.
Read the Statistics Modification Counter With Three Other Columns
last_updated records when the statistics blob was last updated. rows describes the population when statistics were updated, and rows_sampled describes the sample used. These values are not a live count of the table today. A full sample and a small sample give different context for evaluating an estimate.
The statistics modification counter tracks changes relevant to the leading statistics column since the last update for ordinary disk-based tables. It is not a count of every modification to every column. Multiple statistics on one table can therefore show different counters. Adding them together does not produce the table’s total transaction count.
NULL last_updated occurs when no statistics blob has been created, including some empty or filtered cases. Treat that separately from an old timestamp. In memory-optimized tables, counter behavior differs and includes changes since the last statistics update or database restart. Scope your interpretation to the table type you are actually investigating.
Tie the Statistics Modification Counter to the Leading Column
The histogram is built on the first statistics column. For a multi-column statistics object, the other columns contribute density information, but they do not get separate histograms within that object. Identifying the leading column makes the modification counter much easier to interpret.
SELECT sch.name AS schema_name, t.name AS table_name,
s.name AS statistics_name, c.name AS leading_column,
s.auto_created, s.user_created, s.no_recompute,
s.has_filter, s.filter_definition,
p.last_updated, p.modification_counter
FROM sys.tables AS t
JOIN sys.schemas AS sch ON sch.schema_id = t.schema_id
JOIN sys.stats AS s ON s.object_id = t.object_id
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
JOIN sys.columns AS c
ON c.object_id = sc.object_id AND c.column_id = sc.column_id
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE t.is_ms_shipped = 0
ORDER BY sch.name, t.name, s.name;Look at filters and no_recompute as well. A filtered statistic describes a subset, not the entire table. A disabled automatic recompute policy changes how you interpret pending modifications. Neither condition proves a mistake by itself. Check why it exists and whether it still fits the workload.
I tested this on a 10,000-row table with an index on CustomerID and Status and a separate statistic on Status. Four updates of 3,000 rows each changed only Status. The Status statistic showed a counter of 12,000, while the index statistic led by CustomerID stayed at 0.
I check which statistics the plan uses before scheduling an update. A high counter on an unrelated column is a poor explanation for the slow statement. What changed in the distribution used by this query? That question connects the metadata to the problem instead of turning the counter into a maintenance scoreboard.
Understand When Automatic Updates Trigger
With AUTO_UPDATE_STATISTICS enabled, SQL Server detects stale statistics and updates them when compilation needs them. Crossing a threshold does not start a timer that immediately refreshes every object. A statistics object that no current query needs can remain untouched despite a large counter.
Recent versions at compatibility level 130 and higher use a dynamic threshold for larger tables. For ordinary tables with more than 500 rows, the documented threshold is the smaller of two values. One is 500 plus 20 percent of n, and the other is the square root of 1,000 times n. Here n is the population when statistics were evaluated.
Smaller permanent tables use a different threshold, and temporary tables have their own cases. Older compatibility levels also require different interpretation. Do not apply the larger-table formula to every row in the report. Check the engine, compatibility level, and statistics type before labeling an object overdue.
Check the Database Policy and Change Proportion
Read the automatic statistics settings from the current database. Asynchronous updates change whether compilation waits for the refresh. They do not make the old statistics accurate while the background operation is pending. Choose that policy for the workload rather than treating it as a universal speed switch.
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();
SELECT TOP (30) OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
OBJECT_NAME(s.object_id) AS object_name,
s.name AS statistics_name, p.rows, p.modification_counter,
CAST(100.0 * p.modification_counter / NULLIF(p.rows, 0)
AS decimal(19,2)) AS modification_percent
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY modification_percent DESC;The percentage compares recorded modifications with the statistics population. It is a diagnostic ratio, not a universal update threshold. Repeated modifications can drive it above 100 percent, and the Status statistic from the earlier test reported 120.00. It does not mean that more than every row exists. A high score earns no prize here. Do not let an interesting percentage outrun its definition.
Review the count and proportion together. A large absolute count on a much larger population and a small count concentrated in a critical range need different attention. Counter values do not describe the exact shape of a changed distribution. Use the histogram and the query’s estimates to complete that picture.
Update the Object That Explains the Estimate
The function complements DBCC SHOW_STATISTICS. It does not replace the histogram and density information that command provides. Use DBCC SHOW_STATISTICS for the named object when the question concerns data distribution. Keep the correct schema, table, and statistics name from your report.
When an update is justified, schedule a targeted UPDATE STATISTICS with the sampling policy your workload requires. FULLSCAN reads the relevant data and has real cost. Do not refresh every statistic with FULLSCAN simply because one query has a bad estimate. Preserve the original plan and properties so you can explain the change.
After the update, rerun the properties query. Check last_updated, rows_sampled, and the counter, then inspect the query again under comparable parameters. New writes can begin accumulating immediately. A counter that is no longer zero does not prove the update failed. The database has resumed doing normal work.
Keep the Statistics Modification Counter in Its Proper Role
Do not schedule index rebuilds merely to reset statistics counters. Rebuild behavior differs by index type and operation, and column statistics require separate thought. If the issue is statistical information, address that information directly. An expensive maintenance operation should have its own reason to exist.
Keep a focused record of the statistics object, the query that used it, and the evidence for the chosen update. Compare estimated and actual rows along with application behavior. A newer date is easy to obtain. A better estimate requires that the change address the relevant distribution.
Use the statistics modification counter as an investigation aid. It helps identify changed statistics, but it cannot replace plan review, sampling analysis, or workload context. The best maintenance decision explains why this object needs work now and how the affected query will be checked afterward.
A modification counter is not a table’s complete change history, it is evidence about one statistics object.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
