Doubling response times
Recently we had it going on with a client. A database had not been properly maintained for ages, when we were called in. We managed to reduce the application’s response times from an average of 2.8 seconds back to 1.4 seconds. Twice as fast! What was wrong and what did we do?
Fragmentation
By performing insert, update and delete statements on tables, indexes eventually become fragmented. This means that although space has been reserved for an index, there is no longer any useful information in that location.
The fragmentation of such an index is a sign to the database engine which action is needed to perform an operation: a table scan or an index scan. Per platform, the tipping point at which maintenance is required varies. With SQL Server, if fragmentation is below thirty percent, you can consider a “reorganize index.
Smart ways to maintain a database
Does this phenomenon occur only on certain database platforms? No, this is the case on all platforms. It does not matter whether you use MySQL, or MariaDB, Oracle, Sybase, SQL Server, PostgreSQL orNoSQL-varianten such as MongoDB. However, the solution does vary by platform.
Often there are standard features, 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 avoid these intrusive actions.
Database maintenance plan
A maintenance plan can be implemented for each system or database. Several options are configurable for maintenance, ranging from very invasive to risk-averse. Rebuilding an index at a time when the database is in use will impact performance.
This is not pleasant for the user, so it is advisable to schedule such a rebuild in a maintenance window. All kinds of variants are possible here, depending on the setup at the client 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 work week or day.
Slowing application as a virtual warning light
Such a slower application is actually like a light on your dashboard. The other day I was talking to a friend who is a mechanic with the ANWB. He is regularly called to rush to a breakdown.
More than once the car owner then says, full of surprise, “Well, there has been a red light on the dashboard for three weeks. That might have been a sign to go to the garage? An application that suddenly becomes a lot slower is actually the equivalent of the red light on the dashboard: time to take action.
Maintenance pays off!
It is not wise to wait until the moment that the performance of your environment leaves something to be desired. In practice, in nine out of ten cases, overdue database maintenance only comes to light when we do a HealthCheck or QuickScan – that is, when the virtual ANWB mechanic drops by. A shame, the configuration and hardware are capable of much better performance.
Performing and repairing overdue maintenance in most cases costs more time (and thus money) and sometimes damage is even done. The disappointing performance of your database environment can even delay your primary process, incurring 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 if a maintenance plan can improve the performance and lifespan of your database environment and how exactly it works? Feel free to contact us by clicking on the link below.