Database Maintenance Tasks

Queries executed on a database that is regularly maintained, for example table statistics updated on a weekly basis, will run faster than on a database which hardly receives such maintenance.

The BAM service is capable of executing a few common maintenance tasks, but only on the BAM database:

Important

These tasks are not meant to replace any real DBA maintenance of a database.

On the contrary, it is strongly advised to set up such maintenance jobs on the database side as that is the more preferable way of working, and not using the built-in tasks in the BAM service.

Basically the built-in tasks are only meant as a fall-back scenario when for whatever reason, a real database maintenance job cannot be activated, e.g. the database is not under support of a DBA.

Another reason is that these tasks are executed with fixed settings to avoid configuration overload on users. However, as a consequence the execution may take longer because the chosen settings may not always have the proper value for the current state the database is in at that moment.

Take a look at the SQL Server Performance - Create Maintenance Plan for a full example on how to set up such plans.

More information about configuring the execution of these tasks, please consult this article Configuration.

Order of Execution

All maintenance tasks are executed in a particular order:

  1. Restructuring indexes.
  2. Updating table statistics.
  3. Shrinking database files.

The reasoning behind this, is the fact that during rebuilding or reorganising not all table statistics are updated, only some. And as the first 2 tasks may increase the file size, shrinking is done as the last step to avoid doing that step twice.

Also, these tasks are executed right after the cleaning task has completed.

Note

Partitioned Log Table

In case the log table is partitioned, the maintenance tasks of restructuring indexes and updating table statistics will behave the same. But instead of applying those tasks on the log table as a whole, only the last 7 partitions (compared to the start date/time of the tasks) are selected for the maintenance.

The reason is simple: how older a partition is, the more unlikely it will be that such a partition has been changed with new data added or data removed.

Restructuring indexes

SQL Server supports 2 options on index restructuring:

  • rebuilding indexes,
  • reorganising indexes.

The main difference between them, is what the name says: rebuilding is dropping the index and re-creating, while reorganising means a form of defragmentation of the stored data. Unlike reorganising an index, which is always an online operation, meaning it can be done without holding long-term locks, rebuilding on other hand can either be an online or offline operation and uses more resources.

Microsoft advises to use reorganising indexes unless only a rebuild can improve the performance. Ometa follows this advise and this means the configuration option UpdateIndexes should be set to Reorganize if you would like to enable this task.

For a more detailed and technical explanation, read this Microsoft article: Optimize index maintenance to improve query performance and reduce resource consumption

Updating table statistics

Statistics are used by the query optimiser to construct the best execution path for each query that SQL Server is requested to execute.

When these statistics aren't in line with the current data in a table, the query optimiser will select a suboptimal execution path at best but that can have a significant performance impact. Although the query optimiser will update statistics when necessary during query execution, updating those statistics at a regular, e.g. weekly, basis with a separate task can improve performance much further.

The maintenance task of the service will only update all statistics when the oldest statistics are older than 5 days. If all statistics on a table are within the 5 day range, the update will be skipped.

For more information about table statistics, read this Microsoft article: Update Statistics

Shrinking database files

This means moving data around in the database itself, so that the files can be made smaller as the unoccupied parts are returned back to the OS.

But this is a practise that Microsoft does not recommend doing on a regular basis as this action may increase data fragmentation within the database slowing down query performance, among others. For this reason, Ometa does not advise either to run this task at a regular interval.

More information about this can be found in the following Microsoft article: Shrink a Database

As already stated in the warning above, try to set up database maintenance tasks in SQL Server first. This provides the most flexibility and fine-tuned capability to improve performance for each database and user scenario.

In SQL Server Performance - Create Maintenance Plan a full maintenance plan is provided as an example to set up on a SQL Server instance.

Only when that is not possible, the built-in tasks can be enabled. In that case, Ometa advices the following settings:

  • Enable index reorganisation (Reorganize).
  • Enable statistics updates.
  • Disable database file shrinking.
Note

Keep in mind that enabling these tasks will increase the execution time of the cleaning/maintenance run as both tasks (cleaning and maintenance) are linked and will be executed as whole.