Periodic maintenance by DBA.nl

“When the fad is resting, the spider is working”. You may not have heard of this expression before, but it is apt for an essential part of the daily tasks of the DBA’s of DBA.nl.

The expression means that without maintenance, decay will soon follow. This certainly applies to database systems. A database is by nature a dynamic environment. There is constant activity in databases, for example:

• Compilation and (re)evaluation of query plans
• Inserts, deletes, updates, data import/export
• Index updates and rebuild
• Update statistics
• Archiving of changes (Oracle archiving, SQL Server transaction log backups)
• Recording of audit records and activity logs

Some of these activities generate “cobwebs” that must be tracked down and removed where necessary. For example, on an active database server, a gigabyte or more of text files (trace files, audit logs) can be created in a relatively short time for possible later troubleshooting or system auditing. Not all of these files are cleaned automatically and that is partly because some files are permanently in use and therefore cannot be removed just like that (especially on Windows). Also, backups sometimes go wrong and leave incomplete backup files, internal audit tables get full, database files get fragmented, etc. Removing this cobweb must therefore be done regularly by hand. But simply deleting, for example, “old” logs is also not advisable. Inspection by trained eyes is definitely necessary to avoid containing notifications that reveal hidden issues. It does happen that a trace file of a database gives indications that a system is not behaving as it should and would benefit from certain adjustments.

This brings us to the second aspect of maintenance, which is proactive: identifying and predicting sub-optimal behavior of database systems that appear to be functioning normally at the time of audit. This is the greatest value of a DBA’s maintenance work.

It would be wrong to think that a SQL Server or Oracle system optimizes itself. Despite the great advances Oracle and Microsoft have made in self-regulated databases, tuning to your environment is still necessary. This is partly because database configuration is always a compromise. CPUs, memory and disk capacity are limited due to (license) costs and it is therefore always necessary to set limits. Where these boundaries are varies by system, and also depends on application behavior and the number of databases you decide to use at any given time (which will also vary over time). In addition, a modern database has a range of adjustable parameters that must fit the workload; usually there is no “always good” value (otherwise the parameter would not have been made adjustable after all….).

DBA.nl and periodic maintenance

Keeping database environments healthy is the essence of the management activities that DBA.nl performs as part of a Service Level Agreement. As part of our SLAs, we offer a periodic check in which we perform cleaning, perform a thorough analysis of the current state of affairs on your database server(s) and report on this and make recommendations. This ensures that there is no undetected trouble and we will let you know if we think it is wise to address certain issues or consider upgrades.

In many cases, customers who have concluded an SLA with DBA.nl will also have database monitoring at their disposal. Any reports from this monitoring are indications of malfunctions and are handled by us as incidents. Our Periodic Maintenance is an extension of this and also focuses on “defects” that are not visible in ordinary monitoring. If there is no database monitoring, the check is also a kind of safety net for unnoticed incidents.

What do we check at Periodic Maintenance?

Storage area

An important limit to database growth is the amount of available storage space. We check whether this appears to be sufficient for the foreseeable future and also look at historical growth of the databases. Of course it doesn’t stop there and the database is also checked (Oracle tablespaces, for example) to see if there are no growth limits in sight. Where possible, any shortages will be addressed and in other cases we will consider expanding physical storage space.

Performance indicators

For each database we look at whether performance indicators give cause for concern and in most cases can immediately make a recommendation for adjustments to database tuning parameters or server configuration. However, as mentioned before, we do not have a server with unlimited capacity. It can therefore happen that a server contains so many databases that not all performance figures can be equally attractive. In that case, we take into account the maximum achievable and no unnecessary hours are spent on marginal improvements; however, configuration changes can sometimes alleviate any suffering. For example, we can make adjustments to times when certain database-internal jobs are scheduled so that server load is better distributed over time.

Configuration errors

Configuring databases remains a human task and can become skewed during the lifetime of a system. Sometimes a database is temporarily modified during application upgrades and it is forgotten to restore it afterwards. Our audit can help detect these types of errors before they lead to problems.

Data protection

All production systems require a good backup. However, without regular checks it is not always possible to see whether a backup is also usable and complete. We look at the results of backup jobs in terms of content and verify that every production database is indeed included in backups, the backup was successful and that any archive deletion or transaction log truncation works properly. If you have a fallback environment with DataGuard, DBVisit or Always On, we ensure that it is properly updated and that the fallback environment does not show any malfunctions or lack of resources.

Support risks

No software without an expiration date… DBA.nl keeps track of which releases of your database software are still supported by the supplier and for how long. If a database release will become out of support in the foreseeable future, we will inform you in good time during the periodic maintenance.

Resource allocation

Not only storage space is limited, this also applies to internal memory and processors. We identify when resources appear to be under pressure so that it can be considered whether adjustments should be made to the allocation of these resources to the databases or whether the resources themselves should be expanded (if possible).

Object and job status

The most important thing is always your data and its “health”. In addition to health checks of the database software, our periodic maintenance also includes a verification of the status of database objects. We check whether the databases contain faulty stored procedures, indexes, views and the like so that you do not work with a (partially) faulty application unnoticed.

Diagnostics

Modern database systems generate a wealth of information in diagnostic log files. This may contain indications that unnoticed errors occur that must be rectified. We use automated analysis to find hidden flaws and can often fix them before they lead to major disruptions.

Statistics

The performance of your systems is highly dependent on the up-to-date information that databases have about the composition of the data. For example, think of the number of rows in a table and their total size, the average filling degree of a column, the distribution of values in columns (cardinality) and much more. We call this information “statistics”. The query optimizer uses these statistics to determine how best to execute a SQL statement. Unlike most (“imperative”) programming languages, SQL is a so-called “declarative” language. This means that an SQL query only specifies what the result should be, but not how it should be reached. The query optimizer determines, based on the information it has about the composition of the data, the presence of indexes, the amount of memory available and more, how the desired end result can be achieved most efficiently. Keeping this information up-to-date is absolutely essential to the quality of the solution generated by the query scheduler. During the periodic check, we check whether the updating of these statistics functions properly.

License check

Although we do not always know which licenses have been purchased by you, we pay close attention to the use of database features that are not covered by basic licensing. We will notify you when we find use of features that require additional licensing; you can check for yourself whether your licenses cover this use. If it is known to us which features you have purchased, we can disable the optional features for you in the event of an unnoticed exceedance.

And last but not least

In addition to the points already mentioned, there are a number of things that we pay attention to, but it would go too far to describe them exhaustively. Our maintenance methodology is regularly updated based on practical experience and newly available functionality in new releases of the databases. As the end result of our periodic maintenance, we provide you with a report that is as clear and complete as possible, on the basis of which, if necessary, a decision can be made to make adjustments to databases or servers. We hope these recommendations will help maintain the health of your databases for many years to come!