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.