Script to Check Statistics Updated Last Time in SQL Server

  • When was the last time statstics were updated for a particular index
  • You can filter it as per you needs to run it faster or for a particular table/index
  • sys.objects, sys.indexes, sys.stats and sys.dm_db_partition_stats
SELECT SCHEMA_NAME(o.Schema_ID) + N'.' + o.[NAME] AS [Object Name], o.[type_desc] AS [Object Type]
      , i.[name] AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date]
      , s.auto_created, s.no_recompute, s.user_created, s.is_incremental, s.is_temporary
	  , st.row_count, st.used_page_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
	ON o.object_id = i.object_id
INNER JOIN sys.stats AS s WITH (NOLOCK)
	ON i.object_id = s.object_id 
	AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
	ON o.object_id = st.object_id
	AND i.index_id = st.index_id
WHERE o.type IN ('U', 'V')
	AND st.row_count > 0
ORDER BY STATS_DATE(i.object_id, i.index_id) DESC 
OPTION (RECOMPILE)

Full details on Microsoft’s Documentation