Generate UPDATE STATISTICS scriptsIn the previous post of this series I provided the script that both determines the indexes that need attention and builds the individual ALTER INDEX scripts. So, now that the indexes have been tended to it’s time to do the same for table and index statistics.
The update statements created are based on the defrag type carried out on the table as well as a few thresholds to determine whether it is necessary to rebuild the statistics. Those thresholds are:
- (Time passed since last update: >3 days
- And Minimum row count of the table: >=1000 rows
- And Percentage change in number of rows: >20% more or less)
- Or Time passed since last update: >30 days
If the table either was not defragged or its indexes were only reorganized, then a full statistics update will be executed. If the clustered index was rebuilt then only the table’s column statistics will be updated.
For a detailed description of how statistics updates and index rebuilding works together see this post.
Read the final part of the series