Get the most frequently modified indexes and statistics
When you're working with SQL Server, understanding your database's statistics is crucial for optimal performance. Outdated or missing statistics can lead to poor query plans and slow execution times. This SQL query is a handy tool to help you identify statistics that have gone stale and might need updating.
Find Outdated SQL Server Statistics with This Handy Query
This script helps pinpoint statistics that have a high modification counter, meaning the underlying data has changed significantly since the statistics were last updated. By focusing on these, you can prioritize which statistics to rebuild or reorganize to keep your queries running smoothly.
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);
This query looks at all objects in your database (excluding system and
internal tables) and joins them with their associated statistics. The key
component here is
sys.dm_db_stats_properties, which provides valuable information
like the modification counter, indicating how many changes have occurred
to the data since the last statistics update. We're specifically looking for
those with a modification_counter greater than zero, ordered
by the highest changes first. This way, you can easily spot and address the
most critical statistics that need attention.
