If you are an SQL Server Consultant/Trainer involved with Performance Tuning and Query Optimization, I am sure you have faced the following questions many times. When is statistics updated? What is the interval of Statistics update? What is the algorithm behind update statistics? These are the puzzling questions and more.
I searched the Internet as well many official MS documents in order to find answers. All of them have provided almost similar algorithm. However, at many places, I have seen a bit of variation in algorithm as well. I have finally compiled the list of various algorithms and decided to share what was the most common “factor” in all of them.
I would like to ask for your suggestions as whether following the details, when Statistics is updated, are accurate or not. I will update this blog post with accurate information after receiving your ideas.
The answer I have found here is when statistics are expired and not when they are automatically updated. I need your help here to answer when they are updated.
Permanent table
If the table has no rows, statistics is updated when there is a single change in table.
If the number of rows in a table is less than 500, statistics is updated for every 500 changes in table.
If the number of rows in table is more than 500, statistics is updated for every 500+20% of rows changes in table.
Temporary table
If the table has no rows, statistics is updated when there is a single change in table.
If the number of rows in table is less than 6, statistics is updated for every 6 changes in table.
If the number of rows in table is less than 500, statistics is updated for every 500 changes in table.
If the number of rows in table is more than 500, statistics is updated for every 500+20% of rows changes in table.
Table variable
There is no statistics for Table Variables.
If you want to read further about statistics, I suggest that you read the white paper Statistics Used by the Query Optimizer in Microsoft SQL Server 2008.
Let me know your opinions about statistics, as well as if there is any update in the above algorithm.
Update1: Thank you Paul Randal for your comment. (the man who wrote DBCC CHECKDB)
After stats are invalidated:
If auto-update-stats is enabled, the first plan that compiles and sees the invalid stats will update them ther and then.
If auto-update-stats-async is enabled, the first plan that compiles and see the invalid stats will use the invalid stats to compile and will cause an entry to be put on a task queue for a background task to update the stats.
If neither is on, they won’t be updated until someone does an sp_updatestats or UPDATE STATISTICS.
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
You’re talking about when stats are invalidated, not when they’re updated – you need to change that in your post.
After stats are invalidated:
If auto-update-stats is enabled, the first plan that compiles and sees the invalid stats will update them ther and then.
If auto-update-stats-async is enabled, the first plan that compiles and see the invalid stats will use the invalid stats to compile and will cause an entry to be put on a task queue for a background task to update the stats.
If neither is on, they won’t be updated until someone does an sp_updatestats or UPDATE STATISTICS.
Thanks
Pinal,
Your link that is related to Paul is not working, I think you should check it!
Otherwise, thank you for this article!
Have a nice day!
Nice explantion by pinal and paul randal. Thanks for the article.
Hi Pinal,
Maybe there’s another thing to consider about statistics: when a database turns to readonly mode, statistics are freezed and never updated until it goes back to read/write state.
regards
ap
Hi Pinal,
I always enjoy your explanations on various issues related to SQL, thanks and keep the good work.
Hey Pinal
How can we read data from a CSV file or Text file using TSQL?
Can we do that.
It mostly helps us when we create static reports from CSV files using SSRS.
Awesome…
This algorithm was changed in SQL 2005… In 2000 it use to see the 20% of column changes in table.
Hello Sir,
I have many databases in my Application. we have so many tables in each DB.
Now i want to delete all the statics from a database in one Query.
Please note : I do not want to delete a static from a single table .
i want to delete all auto created statics from a Database.
Please help me …i shall be thank full to u.
Well you might need to try optimizing your query first. Can you please explain a little bit more of what exactly would you like to delete from each table
Hi All,
I’m aware of this algorithm since a while, and also remarked that INSERTing massively a table does not trigger statistics update.
This 20% rule is a problem if the table is more INSERTed than anything else …
Hopefully there will be a change on this in SQL 11 ;)
Hi Pinal,
Would you recommend putting sp_updatestats in regular db maintenance task if you have Auto Update Stats = 1. Would it be necessary? Also, I have read people complaining about the performance degradation after running sp_updatestats. In what case, there could be potential performance problem?
Hi Pinal,
Does it mean that untill unless we won’t explicitly mention “recompile” with sql query auto update stat won’t work. Then what is the use of AutoUpdate stats options, if we have to mention recompile every time we want to update stats?
Thanks!
Amit