Database Maintenance for Business Central & Dynamics NAV

In very busy and big OLTP databases such as Dynamics NAV and Business Central, data is quite often unevenly distributed. Typically 90% of the data will be held in 10% of the tables and quite often these tables are very active with lots of inserts, updates, deletes and reads operations performed on them. Due to these characteristics, vast majority of the index rebuilding strategies normally used in the wider SQL communities will not work to keep the index in a healthy state. SQL Mantra Tools has come up with a unique index maintenance strategy specifically geared towards such busy and big OLTP databases. Even though it is geared towards busy and big OLTP database, it will equally work well for a variety of databases from very small databases, web backend database to BI databases. Some of its key features can be found below. Without proper index maintenance, performance will suffer in the long run. Our maintenance strategy is “prevention is better than cure”. Our unique index maintenance package will proactively maintain indexes and will take care of routine maintenance such as backups, deleting old session event logs, checking databases for errors and many more with automated alerting features giving organisation a piece of mind.

• Only rebuild index which are fragmented more than 5% on a daily basis. The rest of the index will be rebuilt proactively in a round robin fashion covering different portions of database each day.
• Routine will not Reorganise index as the Reorganise process is single threaded and will not use parallel operation. Hence the execution is much slower.
• Options to override index selection / exclusion on each day’s index rebuild load.
• Fill factor settings are adaptively changed and optimised based on index’s volatility, data type, size and bad page splits.
• Option to terminate/limit the execution of the re-index after a set time period, so that it will not interfere/block busy/critical process.
• Option to log the details of re-index process (such as what index detail, start and end time, page split, old and new fill factor, etc…)
• Ability to re-index using multiple parallel re-index process (only recommended for really large database which has a very good disk system) to reduce the re-index time window. The concurrent re-index sessions will coordinate and share the work load equally among themselves and they make sure they do not block each other by working on different tables and it’s dependent index.
• Option to rebuild index online (subject to having SQL Enterprise Edition).
• Option to target re-index process on any database within the same SQL Server instance.
• If an index is static, it will not be rebuild at all. The routine will prioritise and rebuild index which have the most fragmentation first.
• If time is limited in a day, option to only re-index badly fragmented index (i.e. more than 5%).
• While rebuilding index, options to change the recovery model to bulk logged or Simple to minimise the transaction log space usage. Once completed the original recovery model will be restored.