Get most frequently modified indexes and statistics


SELECT o.[name] AS [Object Name]
	,o.[object_id]
	,o.[type_desc]
	,s.[name] AS [Statistics Name]
	,s.stats_id
	,s.no_recompute
	,s.auto_created
	,s.is_incremental
	,s.is_temporary
	,sp.modification_counter
	,sp.[rows]
	,sp.rows_sampled
	,sp.last_updated
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.stats AS s WITH (NOLOCK) ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE o.[type_desc] NOT IN (N'SYSTEM_TABLE', N'INTERNAL_TABLE')
	AND sp.modification_counter > 0
ORDER BY sp.modification_counter DESC
	,o.name
OPTION (RECOMPILE);

Spread the love