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://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.
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)
In the post you mentioned “If you see a very high modification counter”.. I wonder what value should be considered as “high” ?
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.
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?
with small modification of your script it is also possible to show statistics of indexed views which my may be important to.