Get Last Updated Date for SQL Statistics
STATS_DATE(,) returns the date of the most recent update for statistics on a table or indexed view.
STATS_DATE ( object_id , stats_id )
This function returns datetime value on successful execution or NULL value if there is an error in the argument.
- object_id: This is an ID of the table or indexed view with the statistics.
- stats_id: This is an ID of the statistics object.
Simple note about SQL Statistics:
Statistics in the SQL Server helps the query optimizer to calculate the cost of the query plans. If the statistics are missing or out of date, it does not have correct data to estimate the plan then it can generate moderate or sub-optimized query plan. This may impact the SQL query performance.
More information for Statistics:
This blog post is to show you the use of function STAT_DATE with an example.
Example 1: This SQL query will display all Index statistics of a Database.
SELECT t.name as table_name, i.name AS index_name,
STATS_DATE(i.object_id, i.index_id) AS statistics_last_updated_date,
cast(DATEDIFF(dd, STATS_DATE(i.object_id, i.index_id), getdate()) as varchar(5)) + ‘ Day(s)’ as Days_Since_Last_Update
FROM sys.indexes as i join sys.tables as t on i.object_id = t.object_id
Example 2: This SQL Query will display all table statistics of a database.
SELECT object_name(object_id) as table_name, name AS statistics_name,
STATS_DATE(object_id, stats_id) AS statistics_last_updated_date,
cast(DATEDIFF(dd, STATS_DATE(object_id, stats_id), getdate()) as varchar(5)) + ‘ Day(s)’ as Days_Since_Last_Update
FROM sys.stats where left (name,4) = ‘_WA_’ and object_id in ( select object_id from sys.tables where type =’u’)
These queries help you to identify the statistics which are not up-to-date using ‘Days_Since_Last_Update’ column in the SQL query.