- 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
