5 myths about database maintenance – Unveiled

5 myths about database maintenance – Unveiled

A database is a data set which is held in computer. For instance, the well-known DBMSs include mysql , oracle. The examples of databases include places database, poi database, global places databases, place database. Poi data is an integrated data set. Places data provides data about global places.

Myth 1: Partitions must be made on various file groups

People have a misconception that partitions must be spread over various file groups. Many coding examples use various file groups and this is where myth originates from. One would want multiple file groups just because if all those file groups are available on separate drives and if there might be any requirement to improve the I/O for large range queries. There is no benefit in having different file groups in same drive.

Myth 2: It is necessary to recreate in order to partition a non-partitioned table

An existing table can be partitioned by rebuilding the clustered index or creating one. It is one of the most effective ways to partition the data. In order to break a large table into various partitions on a column, it can be used. The dbcc showcontig command does not work for the indexes that are partitioned while recreating the non-partitioned table.

Myth 3: Partitioning a table that is already existing can be done offline only

There will not be an option for querying whilst the operation period. But, there is availability of the online index feature to rebuild. There is an option called online=on which helps us to query the table during the partitioning operation. If the data is being partitioned and if there is a sliding window, and the data is being added only in the rightmost partitions then the unnecessary overheads can be saved without checking for fragmentation of old partitions.

Myth 4: Partition switching out or in takes minimal time

The reason that splits the operations include switch in and switch out are quick is because they are ‘metadata’ operations which means the actual data is not moving but only pointers to the data are exchanged. The switch operation can be blocked with highly transactional partitioned tables. This is because of the alter table – switch operation requires a schema to modify lock on the tables. In order to solve the blocking issue it is better to set a timeout before any switch operation. Even if the alter table, switch operation blocks it will be only till the timeout duration.

Myth 5: Altering partition is metadata only

The merge and split operation may take longer than expected time. Partition operation is offline operation and may result in data movement and so it becomes resource intensive. If both the partitions that are being merged together are empty then there will be no movement in data and it will remain a metadata operation only. It is necessary to make sure that both the partitions are empty before merging them. If filter is used on partition key column, desired performance over the equivalent query can be expected on the unpartitioned table.


places data can work effectively with large amounts of data at the same time provide great performance if it is well designed. It is important to have a sound partition management and a maintenance plan will ease the challenging problems and help in concentrating on opportunities.