How to Find Outdated Statistics? – Interview Question of the Week #081

Recently in one of the webcast, while I was presenting about statistics and performance tuning, I was asked by one of the attendees that if there is anyway to find out all the outdated statistics based on the dates from the server. This is indeed a very interesting question and I will be happy to explain you here.

Question: How to find Outdated Statistics?

Answer: I wish the answer was very simple. The definition of the outdated is extremely subjective and often misunderstood. I am very confident that after reading this blog post there will be few blog posts disagreeing with the answer. My only request is to add value to this blog post via comment and try to understand my point of view as well.

In SQL Server in reality, there is nothing like outdated statistics. Well, the statistics can get older and they may not be as relevant as they are today. The definition of older is worth understanding as well. Older does not mean how old it is in terms of days, hours or time. Older is defined in terms of how many updates has happened since the last statistics was updated. Again, all of this is again matters in terms of percentages. For example – if a table with 10,000 rows have 100 rows are updated is not the same as a table with 100,000 to 100 rows. It is all evaluated with percentages.

SQL Server has a logic of updating statistics. If the table has more than 500 rows the statistics are updated when there are 500+ 20% of the table rows are updated (I have simplified this logic and have not covered every single scenario).

Update Statistics

Now if you ask me when the statistics are outdated, I will say I will go with SQL Server’s logic. So here are two options for you:

1. If you have left auto update or auto create statistics on, you should not worry at all, SQL Server will make the task itself.
2. If you have left auto update or auto create statistics off, you should manually update the statistics when it reaches the default of 500+ 20% of table rows.

Now, you may argue that you want the statistics to update more frequently, well you can do that manually or setting certain traceflags. This is where we all will have different opinion – some believe that statistics should be updated when change 10% or 15%. It is totally updated you. However, there is no guarantee that SQL Server will give different (or improved) execution plan even though you update your statistics. SQL Server will try its best to build the most effective execution plan all the time.

Here is the script which will give you details about how old index is and how many modifications have happened since the last statistics were updated. Based on this you can make YOUR OWN decision to consider statistics to be outdated and update the statistics.

Script 1: Modification Counter and Last Updated Statistics

-- Script - Find Details for Statistics of Whole Database
-- (c) Pinal Dave
-- Download Script from - https://blog.sqlauthority.com/contact-me/sign-up/
SELECT DISTINCT
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)
ORDER BY DaysOld;

Script 2: Update Statistics for Database

EXEC sp_updatestats;
GO

Reference: Pinal Dave (https://blog.sqlauthority.com)