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.

SQL SERVER - Find Oldest Updated Statistics - Outdated Statistics OutdatedStatistics1-800x339

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://blog.sqlauthority.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.

SQL SERVER - Find Oldest Updated Statistics - Outdated Statistics OutdatedStatistics

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://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER 2019 – Performance Issues After Upgrading from SQL Server 2012
Next Post
SQL SERVER – Scan Count Zero for Statistics IO

Related Posts

3 Comments. Leave new

  • Hi Pinal,
    In the post you mentioned “If you see a very high modification counter”.. I wonder what value should be considered as “high” ?
    Thank you,
    Priyanka

    Reply
    • It is a relative number and you should compare it with the number of rows in the table and decide if the counter is high or low in relation to that.

      Reply
  • If memory serves, MOST of the time updating statistics frequently is a good practice as long as it isn’t impacting production as it will use resources to recalculate the statistics. I am not aware of any issues with updating statistics too frequently (unlike rebuilding or reorganizing indexes too frequently which can result in performance hits due to page splits).
    Is there any reason not to do daily statistics updates as long as you have a good downtime window?
    This is assuming you are on SQL Server Standard edition.

    I suppose it is just wasted resources if you update statistics on a table that has no data changes. Are there any other downsides?

    Reply

Leave a Reply

Menu