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).
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:
- If you have left auto update or auto create statistics on, you should not worry at all, SQL Server will make the task itself.
- 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)
Pinal, can you address the complications of SQL Server in a Sharepoint environment? It’s my understanding that both auto-create and auto-update statistics should be turned OFF.
I also heard the same from other clients as well. Looks like this is Microsoft’s recommendation.
Produces “Invalid object name ‘sys.dm_db_stats_properties’. error on our SQL Server 2008R2
This query works with SQL Server 2012 and onwards.
The query works on SQL Server 2008 R2(SP3)
The logic of 20% you described is valid only until 2014. Since 2016 the threshold depends on the number of rows in the table.
Because the trace flag 2371 has been promoted to default behavior in SQL 2016 and cannot be disabled: with 2008, if you want Sql Server adjusts dynamically the threshold to the number of rows in the table to determine when statistics are old, you have to enable such trace flag and set AUTO_UPDATE_STATISTICS_ASYNC ON on table.
Pinal, This doesn’t appear to work on SQL Server 2016. I’m getting a “invalid object name ‘sys.dm_db_stats_properties’. Do you have a fix for this?
Thanks for everything you do for us!
I have Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) – 13.0.4435.0 (X64) Enterprise Edition (64-bit) and the query works just fine. Besides – as I wrote in a previous reply – it works in 2008 SP3 version too. I suspect you have a RTM version. Paste output of query ‘SELECT @@VERSION’ here to check. bye
What is the history of this database? Any errors in applying any service pack /patch earlier? These objects should be part of resource database.
I have a table with partition by date, this table has around 5B records and 20M records insert every day.
This is good idea to update statistics only for the last 2 partitions? or we should also run update statstistcs on whole table?
You can use the Incremental Statistics feature available from 2014 version of SQL server.
I have several databases on Azure on 1 SQL Server and in an Elastic Pool.
By default ‘Auto Create Incremental Statistics’ is ‘False’. Also ‘Auto Update Statistics Async’ is ‘False’.
Should I enable it on my databases?
I can’t find any conclusive anwers anywhere.
This script does not show the statistics that were created when indexes were created on tables or view.
Nome de objeto ‘sys.dm_db_stats_properties’ inválido.
I have Microsoft SQL Server 2016 SP2 (X64) Enterprise Edition (64-bit) and the query works fine too.