Defragging Indexes Without Losing Index Stats - Part 4

Generate UPDATE STATISTICS scripts

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

Once again, if you’d like to jump to the end and download the full stored procedure I've created to maintain all the databases on a SQL Server instance, you can download it here. CAVEAT (as always): The stored procedure has been tested but I provide no guarantees to any of the scripts found on this blog. 

Read the final part of the series