SQL SERVER – Find Oldest Updated Statistics – Outdated Statistics

During the recent Comprehensive Database Performance Health Check client asked me if I have a script that can help them to find out the oldest updated statistics. Essentially they wanted to know which statistics in their database have been not updated for a while. The word which they had used was Outdated Statistics. Let us learn more about it in today’s blog post.

I personally do not agree with the word Outdated Statistics. In SQL Server, it is quite possible an index is many years old but still, it is very much useful and there is no need for it to get updated. If your table is not updated frequently (or a static table), it is totally possible that your index is many years old and still may be absolutely valid.

Earlier I have created a script that was demonstrating the statistics for the auto-created statistics. Today I am posting the script which is updated from the previous script. In this script, I have added a schema name as well as all the statistics for the indexes as well.

-- Script - Find Details for Statistics of SQL Server Database
-- (c) Pinal Dave
-- Download Script from - https://darkslategrey-bat-805937.hostingersite.com/contact-me/sign-up/
SELECT DISTINCT
OBJECT_SCHEMA_NAME(s.[object_id]) AS SchemaName,
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
DATEDIFF(d,STATS_DATE(s.[object_id], s.stats_id),getdate()) DaysOld,
dsp.modification_counter,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id
FROM sys.stats s
JOIN sys.stats_columns sc
ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
CROSS APPLY sys.dm_db_stats_properties(sc.[object_id], s.stats_id) AS dsp
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
-- AND (s.auto_created = 1 OR s.user_created = 1) -- filter out stats for indexes
ORDER BY DaysOld;

When you run the query above it will give you many essential details about your statistics. The columns which you carefully look at are LastUpdated, DaysOld, and Modification_Counter. Those are the columns which are essential I would look to understand the statistics.

If you see a very high modification counter with a LastUpdated very old. I would carefully investigate that index and maybe will just update them to be on the precautionary side.

Here is a script you can run for the entire database.

EXEC sp_updatestats;
GO

Let me know what you think about this blog post and if you want me to build a SQL in Sixty Seconds video on this topic. Here is another blog post that is relevant to this blog as well as SQL SERVER – Not Auto-Updating Statistics with STATISTICS_NORECOMPUTE.

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)