Blog Post

Query to determine last statistics update for a database

,

Statistics are a vital part of SQL Server database engine, these are used for the query optimizer to create query plan.
These statistics store distribution information about one or more columns on tables and views, you can read more about how statistics works from Microsoft documentation in the first link.

On this post I am sharing a lightweight query to determine last update statistics date for each user table on a database. Please note that objects with null values are objects without statistics.
the ones with _WA_Sys names, are the ones generated automatically when you have enabled the AUTO_CREATE_STATISTICS option.

SELECT obj.name [table name],
sp.stats_id,
stat.name [stat Name],
stat.filter_definition,
sp.last_updated,
sp.rows [object rows],
sp.rows_sampled,
sp.unfiltered_rows
FROM sys.objects obj
LEFT JOIN sys.stats AS stat --Include objects without statistics
ON obj.object_id = stat.object_id
OUTER APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE obj.type = 'U' -- Only user tables
ORDER BY [table name],
last_updated

Remember that you can update them using the UPDATE STATISTICS option.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating