Centralized & Customizable DB Maintenance Plan for SQL server

It has been more than a year since the world is going through a pandemic situation and many of us are working from homes. Teams and clients are connected virtually, and everyone is busy with their own day to day responsibilities. Today, I thought of sharing one of my implementations in the field of database maintenance for SQL server, which made the DBA team’s life easy and it brought good stability to the client mission critical applications. This solution was implemented using the open source database maintenance command utility, which generates Index and Statistics related maintenance commands. We have created various surrounding component to the maintenance command utility to work it like DB maintenance tool. This solution has given us centralized control, scalability & reusability features with low cost.

We have used SQL server & T-SQL to implement the entire solution where we made it flexible by keeping all configurations and mappings in the database tables. The definition for DB maintenance plan can be easily changed by making simple changes to configuration tables in a dedicated DBA database.

 “DB Instances & Databases Config” component is used to keep mapping of the DB instances and corresponding Databases with flag for DB maintenance & other control flags. We have the concept of windows which further mapped to “DB Instances & Databases Config” & also associated with schedule i.e. time when you would like to run the “Maintenance Plan Command Generator” component. You can have multiple windows for a SQL instance and sets of databases. The schedules are nothing but the SQL server agent jobs to be run at defined time, which is also mapped to windows and Queues in DB config tables. We have made dynamic T-SQL script utility which helps to generate SQL server agent jobs from the mapping and config table but it’s in beta version.

“Maintenance Plan Command Generator” refers to the “Maintenance Plan Parameter Config” which holds configurations detail for DB maintenance command generator utility for run parameters for each window and database combinations. “Maintenance Plan Command Generator” is the Core component of the solution since it is used to check the Index and Statistics health and generate the maintenance command for their maintenance for the set of databases of the SQL instance. The details for the Index and Statistics health is captured in the “DB Index/Stats Health Log” component as the PRE-condition before the DB maintenance. The maintenance commands for databases are captured in the table and mapped to “Default Queue” initially.

Queue is nothing but the logical container which holds list of tables related to indexes/statistics for DB maintenance commands. Next step is, “Custom Logic to populate Queues” which again refers to the “Maintenance Plan Parameter Config” which holds each Queue and the Tables mapping configuration for each database with priority for maintenance. Queue and the Tables mappings configuration can be changed based on application database behavior and the way you want to schedule it to run during DB maintenance windows. “Custom Logic to populate Queues” helps to put the Tables related DB maintenance command in specific Queues as configured, other than “Default Queue”. The Command Set 1…N to Queue 1…N population is shown in diagram for better clarity. “Custom Logic to populate Queues” also helps to validate and apply various custom rules applicable to Applications databases through specific modular T-SQL code.

The Queues having DB maintenance commands are executed one by one as per the associated schedule. The details for the Index and Statistics health is captured again in the “DB Index/Stats Health Log” component as the POST-condition, once the entire Queue commands are executed. The history of Index and Statistics health from “DB Index/Stats Health Log” component helps to understand database indexes/stats behavior for database. This information is made available on time scale, so it provides greater visibility and enable DBA to take actions proactively. “Maintenance Log” component captures the progress status of each step within the DB maintenance flow at very granular level. “Maintenance Log” based reports are quite useful to get the Live status & it gives full control to DBA to stop/start the Queue/Database maintenance processing. Each command within the Queues goes through various validations based on configurations and scheduled time windows, before it gets executed. The Queue execution also has Pause functionality, based on custom checks of application condition i.e. the Queue goes on hold (with 5 mins delay) if business critical process is in progress & resumes back if it again satisfies the application condition. We have such several custom checks implemented.

The config components like “Maintenance Plan Parameter Config” & other mappings requires modification, based on DB & application behavior, DB “maintenance log” & “DB Index/Stats Health Log” component details and the maintenance window need. The DBA must revisit and make changes to the config and mapping to meet the overall application performance requirements. The solution was implemented successfully for couple of mission critical applications with multiple TB of databases.

I tried my level best to explain the complex architecture in simplest and generic way. I am intending to come up with more such articles in future. Hope you enjoyed reading it 😊.