I have seen this question being asked in many forums and many developers have asked this question to me from time to time. So I thought it would be better to share my script that I use very often to check when the statistics were last updated on all indexes in a database. This script has helped me a lot to discover the problems with out-of-date statistics as well as it gives me an idea about the most used indexes in the databases.
SELECT obj.name, idx.name AS [Index Name],
STATS_DATE(idx.[object_id], idx.index_id) AS [Statistics Date],
s.auto_created, s.no_recompute, s.user_created, part.row_count
FROM sys.objects AS obj WITH (NOLOCK)
INNER JOIN sys.indexes AS idx WITH (NOLOCK)
ON obj.[object_id] = idx.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON idx.[object_id] = s.[object_id]
AND idx.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS part WITH (NOLOCK)
ON obj.[object_id] = part.[object_id]
WHERE obj.[type] = ‘U’
ORDER BY STATS_DATE(idx.[object_id], idx.index_id) ASC;
In AdventureWorks database it is giving me the following results:
Hhmm…I guess I need to update the statistics.