Fragmentation
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!