SQL SERVER – When are Statistics Updated – What triggers Statistics to Update

Update: Thank you Paul Randal for your comment. With your comment, I think now this post is complete.

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: As per Paul Randal (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 (http://blog.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – When are Statistics Updated – What triggers Statistics to Update

  1. 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

    Like

  2. 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!

    Like

  3. 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

    Like

  4. 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.

    Like

  5. Pingback: SQL SERVER – Puzzle – Statistics are not Updated but are Created Once Journey to SQLAuthority

  6. Pingback: SQL SERVER – Solution – Puzzle – Statistics are not Updated but are Created Once Journey to SQLAuthority

  7. 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.

    Like

  8. 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 ;)

    Like

  9. 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?

    Like

  10. 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

    Like

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s