- 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