The benefits of database maintenance

By: Martijn Wallet 01-09-2022

Categories:Blog, Review, Technology,

If a red light on your dashboard turns on, most people call the garage - or the roadside assistance - with lightning speed. But if an application suddenly slows down, few people are inclined to do anything about it. That’s a missed opportunity. In this blog Martijn Wallet explains the benefits of having your database regularly serviced.

Doubling the response times

Recently we had something like this going on with a customer. A database had not been properly maintained for ages by the time we were called in. We were able to reduce the response times of the application from an average of 2.8 seconds to 1.4 seconds. Twice as fast! What had been wrong and what did we do about it?

Fragmentation

If insert, update and delete statements are performed on tables, indexes eventually become fragmented. This means that although space is reserved for an index, there is no longer any useful information in that location. The fragmentation of an index tells the database engine which action is needed to perform an operation: a table scan or an index scan. For each platform, the tipping point at which maintenance is required varies. With SQL Server, if the fragmentation is below thirty percent, you might consider a ‘reorganize index’.

Smart ways to maintain a database

Does this phenomenon only occur on certain database platforms? No, this is the case on all platforms. It doesn't matter if you use MySQL, or MariaDB, Oracle, Sybase, SQL Server, PostgreSQL or NoSQL variants such as MongoDB. However, the solution does vary depending on the platform. Often standard solutions are available, but they are intrusive 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 tools, to minimize or even prevent these intrusive actions.

Database maintenance plan

A maintenance plan can be implemented for each system or database. Several options can be set for maintenance, ranging from very drastic to risk-avoiding. Rebuilding an index at a time when the database is in use will impact performance. This is not convenient for the user, so it is advisable to schedule such a rebuild during a maintenance window. All kinds of variants are possible, depending on the setup at the customer and the type of application. Implementing such a maintenance plan is an iterative process. You have to carefully monitor and coordinate the chosen maintenance and the degree of fragmentation during the working week or day.

Slow application as virtual warning light

A slower application is actually like a dashboard light. The other day I was talking to a friend who is a mechanic with the roadside assistance service. He is regularly called upon to rush to a vehicle breakdown. More than once the car owner says, in amazement: ‘Well, there has been a red light on the dashboard for three weeks.’ Maybe that was a sign to go to the garage?? An application that suddenly slows down is actually the equivalent of the red light on the dashboard: time to take action.

Maintenance pays off!

It’s not wise to wait until the performance of your environment is noticeably poor. In practice, in nine out of ten cases, overdue database maintenance only comes to light when we perform a HealthCheck or QuickScan – in other words, when the virtual mechanic comes by. A shame, because the configuration and hardware are capable of much better performance. Performing and restoring overdue maintenance usually costs more time (and therefore money) and sometimes even damage has been done. Disappointing performance of your database environment can also slow down your primary process, causing unnecessary costs in this area as well. Through smart monitoring and a (partially) automated maintenance plan you can detect delays or potential disruptions in time and even prevent them. Is it already time for maintenance?

Want to know more?

Curious whether a maintenance plan can improve the performance and life span of your database environment and how it works exactly? Feel free to contact us without any strings attached! We are happy to work with you on this.

Back to blogoverview