SQL SERVER – Statistics Modification Counter – sys.dm_db_stats_properties

The Statistics Modification Counter tracks changes relevant to each statistics object. I inspect it alongside the affected query and estimates.

A customer asked this question during a performance health check: which table had changed most since its statistics update? sys.dm_db_stats_properties gives us a practical place to start, with one row per statistics object.

SELECT OBJECT_SCHEMA_NAME(obj.object_id) AS schema_name,
       obj.name AS table_name, obj.object_id, stat.name AS statistics_name,
       stat.stats_id, sp.last_updated, sp.modification_counter
FROM sys.objects AS obj
JOIN sys.stats AS stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE obj.type = 'U'
ORDER BY sp.modification_counter DESC, obj.object_id, stat.stats_id;

For ordinary disk-based tables, modification_counter tracks changes to the leading statistics column. Multiple statistics on the same table have separate counters. Don’t sum them and call the result a table-wide update count.

Interpret the result before changing statistics

last_updated identifies when the statistics blob was generated. It can be NULL when no blob exists. CROSS APPLY excludes statistics for which the function returns no row, including inaccessible objects.

The function reports statistics properties; it does not replace the histogram and density information shown by DBCC SHOW_STATISTICS. Memory-optimized tables also have different counter semantics, so check the documented scope.

A large counter or old date doesn’t prove a bad estimate. I inspect the query, histogram and actual versus estimated rows. Automatic updates help maintain statistics. Their thresholds and timing don’t replace investigation of a particular problem.

Related reading

• Comprehensive Database Performance Health Check

A statistics counter is not a table-wide change total, it is a property of an individual statistics object.

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