Regular maintenance keeps your SQL Server buzzing like a charm. If you skip maintenance, performance will degrade gradually and data might get lost. In the end, your users will notice. So you do need to perform maintenance, even on an Azure SQL Server Managed Instance. Fortunately, maintenance can be automated and monitored so you’ll know when you need to act. We’ve created scripts to “set and forget” that even distributes the load to reduce the maintenance impact on production. The script sends out a daily report, pointing out any points of attention when applicable.

Maintenance jobs

Every 30 minutes a SQL Server Agent job kicks off the script. The script checks whether a maintenance job is running. If there isn’t any, the next maintenance job is started. This ensure the maintenance load on the SQL Server is distributed.

The type of maintenance to perform can be specified per database. The script currently support three types of common maintenance:

1. Rebuild indexes;
2. Update statistics;
3. Check database.

Rebuild indexes

Indexes are not stored contiguously on a disk and the database becomes fragmented. More disk reads result in degraded performance. Rebuild indexes can have a significant performance benefit.

When fragmentation exceeds 30% on larger tables, the script makes sure the index is rebuilt.

Update statistics

Statistics are the key to better optimization and therefore better performance. The query optimizer uses statistics to help make decisions when creating an execution plan. Out-of-date statistics can mean a much less optimal plan.

The script runs sp_updatestats to optimise execution plans.

Check database

Although Microsoft checks for physical corruption, customers should be checking for logical corruption.

The script runs DBCC CHECKDB to keep an eye on disk corruption.

Monitoring maintenance jobs

All maintenance jobs are logged for reporting and auditing. The log is queried once a day by using an Azure Function App on a timer via cron. The app reports the performed maintenance in a Slack channel. Jobs that need attention stick out by using an image of a red cross.

Installing the script

SQL code is to provided for initial setup of the overall maintenance table structure and supporting stored procedures. Just run a .sql-script and you’re set.

The setup required for notifications depend on the app, in case of Slack:

1. Create a Slack channel;
2. Create a Slack App to process incoming webhooks;
3. Associate the Slack channel with the Slack app;
4. Configure the Function app to send notifications.

Using the script

The setup script creates three stored procedures to ease using the solution:

1. sp_AddDatabase: Stored procedure to add maintenance jobs for a database. For example: exec sp_AddDatabase 'chrisbplayground',1,1,1
2. sp_RemoveDatabase: Stored procedure to remove maintenance jobs for a database. For example: exec sp_RemoveDatabase 'chrisbplayground'
3. sp_MoveToNextActionOrMarkCompleted: Stored procedure to push maintenance for a specific database the top of the queue. For example: exec sp_MoveToNextActionOrMarkCompleted 'chrisbplayground'

Would you like to know more?

The scripts were developed for an Azure SQL Server Managed Instance and alerting via Slack. Of course, we can extend the scripts to support Azure SQL or even an on-premises instance and alert via for instance Microsoft Teams. The scripts are easily extentible to include other types of maintenance. Let us know if you’re interested by leaving a comment below.