If you are an SQL Server Consultant/Trainer involved with Performance Tuning and Query Optimization, you have likely encountered questions about statistics. When are statistics updated? What is the interval of statistics update? What is the algorithm behind updating statistics? These questions can be puzzling, but fear not, we have done the research for you.

After scouring the internet and official Microsoft documents, we have compiled a list of various algorithms used to update statistics. While there may be some variation in the algorithms, we have identified the most common factor across all of them.

Let’s dive into the details of when statistics are updated for different types of tables:

Permanent Table

• If the table has no rows, statistics are updated when there is a single change in the table.
• If the number of rows in a table is less than 500, statistics are updated for every 500 changes in the table.
• If the number of rows in the table is more than 500, statistics are updated for every 500+20% of rows changes in the table.

Temporary Table

• If the table has no rows, statistics are updated when there is a single change in the table.
• If the number of rows in the table is less than 6, statistics are updated for every 6 changes in the table.
• If the number of rows in the table is less than 500, statistics are updated for every 500 changes in the table.
• If the number of rows in the table is more than 500, statistics are updated for every 500+20% of rows changes in the table.

Table Variable

There are no statistics for Table Variables.

It is important to note that the above algorithm is based on our research and may not be entirely accurate. We would appreciate your input and suggestions to improve the accuracy of this information. Please share your thoughts in the comments section.

Update 1: We would like to thank Paul Randal, the man who wrote DBCC CHECKDB, for his comment. After stats are invalidated:

• If auto-update-stats is enabled, the first plan that compiles and sees the invalid stats will update them then and there.
• If auto-update-stats-async is enabled, the first plan that compiles and sees 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 enabled, the stats won’t be updated until someone manually executes an sp_updatestats or UPDATE STATISTICS command.

If you want to read further about statistics, we suggest that you refer to the white paper “Statistics Used by the Query Optimizer in Microsoft SQL Server 2008”.

Thank you for reading and we look forward to your opinions and any updates to the algorithm mentioned above.