How does SQL Server know when to update statistics?
SQL Server follows a very specific set of rules on when it should update the statistics of an index. Here they are:
- If the number of rows in a table are greater than 6, but less than or equal to 500, then statistics are automatically updated when there have been 500 modifications made.
- If the number of rows in the table are greater than 500, then updates are automatically made when (500 plus 20 percent of the number of rows in the table) have been modified.
If you like, you can check to see how many modifications have been made to a table, and at the same time estimate when an automatic statistics update will occur. If you go to the sysindexes table of the database in question, and look at the rowmodctr column, it will show you what the count is.
select rowmodctr,* from sys.sysindexes
From this number, you can estimate when the next automatic update of statistics will occur.