Database Preventive Maintenance

The single most common problem I see when performing database health checks is a lack of preventive maintenance.

There are a few prevalent reasons for this:
- DBMS’ are frequently maintained by people other than experienced DBAs. This leads to problems due to lack of knowledge.
- Owners (both business owners and line of business owners) are often adamantly against performing any work that might slow down the system. Preventive maintenance will often slow down the system.

Database Preventive Maintenance

Let’s take a moment and define preventive maintenance. We’ll assume you’re doing the simple things, which include making full backups, moving them off-site, and taking incremental backups, which gives you point-in-time recovery of your data.

Preventive maintenance, for the very focused point of this article, refers to critical tasks which are time & resource consuming, but essential for the health of your system. This includes both index rebuilds and database consistency verification.

Rebuilding Indexes is the process of rebalancing the b-tree structures that the DBMS uses to access your data efficiently. If you do not rebuild your indexes as they become imbalanced over time (how much time? Anywhere from hours to weeks, depending on the volume of change in your system), then performance will degrade, and frequently become very erratic. The unfortunate thing about index rebuilds is that in a few versions of some DBMS, this can cause blocking and other application slow-downs while the rebuilds take place. The catch is, without the rebuilds, performance degrade, spiraling out of control.

Verifying database consistency is the only way you can guarantee that once backed up, you can restore and access the database. Verifying database consistency is the process of walking page headers, object allocation maps, internal structures, etc., to make sure that there are no corruptions in the code.

As you might suspect, both processes are extremely io intensive and can take a while to complete. As you would probably guess, it’s best to perform this type of activity during off-hours if that’s available.

Why are People Afraid of Maintenance?

The primary reasons seem to be that until the performance is affecting end users, the cost of performance slow-downs doesn’t override the cost of the maintenance. Stated otherwise, people are maintaining their databases via wishful thinking.

This is more dangerous than it sounds. We had a client who not too long ago found that because of a database corruption, was completely shut down. When going to backups, it turned out that the backup process was faulty (a third-party product they’d spent seven figures on) and did not have a good (i.e., non-corrupt) backup more recent than six months prior. Secondary note: it doesn’t help to be performing backups if you can’t work on the restored data set.

We’ve also been brought in on many occasions where the only way to help to improve performance had been to rebuild indexes.

It’s important to make sure that standard maintenance is performed and current, otherwise, your systems are at risk.

Author Jeff Garbus