Direct naar content

The Need for Database Maintenance

It actually always comes to light during a Health Check, QuickScan or tuning that OptimaData performs for clients. Overdue database maintenance! When there is overdue database maintenance on the database system, the current resource usage will not be representative of what the configuration and hardware could be capable of.

Below is the result of a successful implementation of database maintenance. Application response times went from an average of 2.8 seconds to about 1.6 seconds.

Martijn Wallet

Principal DBA Consultant
Ellert van Koperen


Performing insert, update and delete statements on tables will cause indexes to become fragmented. This means that space is reserved for an index but there is no longer any useful information in that location. The database engine uses the fragmentation of an index, among other things, to determine which action to perform; a table scan or an index scan.

The tipping point at which maintenance is required varies from platform to platform. With SQL Server, if the fragmentation is below 30%, a ‘reorganize index’ can be considered.

Smart database maintenance ways

Does this phenomenon occur only on certain database platforms? No, on all platforms. MySQL, MariaDB, Oracle, Sybase, SQL Server, PostgreSQL and even NoSQL variants such as MongoDB. The method of solving this varies by platform. Usually there are standard features available but often they are invasive and block normal database processes, such as storing new records.

With licensed platforms such as SQL Server and Oracle, it comes down to paying more to avoid these blockages. However, there are ways, with and without third-party tooling, to minimize or even avoid those intrusive actions.

Database maintenance plan

A maintenance plan can be implemented for each system or database. There are several options configurable for maintenance, intrusive or risk avoiding. An index rebuild during use of the database will have an impact on performance, so it is advisable to schedule it in a maintenance window. There are all kinds of variants for this as well, depending on the setup at the client and the type of application.

Implementing a maintenance plan is an iterative process. One must carefully monitor and align the maintenance chosen and the degree of fragmentation during the work week or work day.

Would you like to talk or have a look at whether a maintenance plan will improve the performance and longevity of your database environment and in what way? Feel free to contact us!