When did the last statistics update occur?

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.

sys.stats
sys.stats_columns
sys.columns
sys.dm_db_stats_properties

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

Leave a Reply

Your email address will not be published.