Statistics are the critical piece of information that SQL Server maintains for a table or index. The query optimization process depends on the statistics information to prepare an execution plan for SQL statements.
This post describes the method to identify the statistics update date and modification count since the last statistics update date. Knowing this information makes it easier to decide whether or not to update the statistics.
SQL Server by default updates the statistics when the auto-update statistics option is ON but it will update the statistics only when there are changes in 20% of the table’s records plus 500 records. The entire process depends on the Cardinality Estimation Model version (CE). The CE considers different factors for auto-update the statistics. So, it is important to have a maintenance routine to update the statistics periodically.
The following system views and dynamic management functions are used to get detailed information about the table, statistics, last update date and the number of modifications.
The following query returns the name of the table, statistics object name, column name, last updated date of the statistics object and modification counter. The modification counter shows the number of records changed since the statistics was created or last updated. The modification counter will reset to zero whenever the statistics update occurs.
select object_name(s.object_id) as object_name, s.name as stats_name, s.auto_created, c.name as column_name, sp.stats_id, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter from sys.stats s join sys.stats_columns sc on s.object_id = sc.object_id and s.stats_id = sc.stats_id and objectproperty(s.object_id, 'isusertable') = 1 join sys.columns c on c.object_id = sc.object_id and c.column_id = sc.column_id cross apply sys.dm_db_stats_properties(s.object_id, s.stats_id) sp