Happy Fall! It’s a beautiful October here in Minneapolis / Saint Paul. In preparation for my home town SQL Saturday this weekend, as well as the PASS Summit, I offer an update to the Rules-Driven Maintenance code I originally published back in August 2012. It’s hard to believe this thing is now more than two years old – it’s been an incredible help as the number of databases and instance my team manages has grown.
One enhancement with this update is the ability to set overrides for both Index and Statistics maintenance on a per-object basis, for example to use full scan on specific tables, or set custom thresholds or exclusions for index maintenance.
Statisics maintenance has gotten a lot smarter, kicking sp_updatestats to the curb in favor of a more intelligent adaptive statistics update based on these findings.
The basic concepts for this solution remain the same: preferences, which act as rules, are stored in an administrative database locally on each SQL Server, and SQL Agent jobs look at those preferences to decide how to manage all the databases on the instance. This structure allows hundreds or even thousands of SQL Server databases to be maintained and backed up using a single code base, with just five jobs.
A detailed description and instructions are available back in the original post.
As with the other version updates, the code here is cumulative, so it can be installed from scratch or over the top of a previous version.
This software is free under the GNU public license.