SQL SERVER – Statistics Modification Counter – sys.dm_db_stats_properties

When you work with the SQL Server Performance tuning area, every single day there are new learnings and discoveries. Recently while I was working at my client’s place on Comprehensive Database Performance Health Check, they asked me if I know a way to know which table has been most updated since the last statistics update. Actually, this is a very interesting question and the answer to this question is also very simple. Let us learn about it in today’s blog post about the Statistics Modification Counter.

Statistics Modification Counter – sys.dm_db_stats_properties

Today we are going to discuss the DMV sys.dm_db_stats_properties. This DMV returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database. This DMV is the replacement of the popular DBCC command DBCC SHOW_STATISTICS.

Today we want to find out which table has been modified the most since the last statistics update. This was not possible to figure out easily from the command DBCC SHOW_STATISTICS. However, in the DMV sys.dm_db_stats_properties we can easily do that with the help of column modification_counter. This column contains the total number of modifications for the leading statistics column since the last time statistics were updated.

SELECT obj.name, obj.object_id, stat.name, stat.stats_id,
last_updated, modification_counter
FROM sys.objects AS obj
INNER 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
ORDER BY modification_counter DESC

When you run the above command it gives very similar results to the following image.

The last two columns are very important. The column last_updated suggests when was the statistics last updated and modification counter column indicates how many times the column was updated since hte last statistics updated.

There are two things to remember here:

1. Statistics are old and do not mean you will get always poor performance. The real driving counter is modification counter.
2. If you have kept auto-update statistics enabled for the database configuration, in most cases, you do not have to worry about these settings.

With that said, there are enough cases above two statements are proven wrong in the Comprehensive Database Performance Health Check.

Reference: Pinal Dave (https://blog.sqlauthority.com)