Dynamics AX – Periodic maintenance and performance analysis

1 Periodic clean up - Significance

A recent AX_Database analysis showed that the 5 largest tables in the database account for roughly 60% of the database size. The 4 largest tables out of 5 are tables with temporary and/or archival data: Inventory settlements (INVENTSETTLEMENT), database log (SYSDATABASELOG), temporary sales orders data (SALESPARMLINE), temporary master planning data (INVENTSUMLOGTTS). The fifth table is sales lines (SALESLINE). Periodically archiving/purging these tables would significantly reduce database size and will have beneficial effect on system performance.

Each individual table and related considerations are discussed below:

1.1 Inventory settlements.

The inventory settlements table is used to store information generated during inventory close and adjust periodic job run. This table can be cleaned up/compressed using the

Inventory management>Periodic>Clean up>Inventory settlements clean up.

Deleting cancelled settlement is a recommended procedure (when an inventory settlement/adjustment is cancelled the system does not remove the original adjustment but generates a set of reverse adjustments). The  clean up procedure will remove both the original set of adjustments and the reversing set.

Please note that Grouping (compressing) settlements should be used with care as compression cannot be reversed and settlements for compressed periods cannot be reversed as well. That means that it is better to use this function only for previous financial years (i.e. group settlements posted before date should typically current date – 1 year).

The Inventory settlements clean up  can be batch-scheduled, takes a few hours to run and is best to be scheduled overnight or on the weekend.

The suggested schedule is once per quarter.

The batch job can be setup to run this procedure over night but the dates will have to be changed before every batch is run. E.g. the batch can be setup to run at 12:00 am to purge transactions of a year ago.

Note: The purge date will have to be changed manually before every batch job as AX will not calculate a rolling date of when transactions need to be purged

Note: Please see the document on Batch job procedures for information in setting up a batch job.

1.2 Database log

The database log table is used to store logging information generated according to the log rules set up inAdministration>Setup>Database log.

There are 2 main points to consider for database log maintenance.

The first one is that there should not be excessive logging. The database log setup should be reviewed and some of the rules (such as logging of all changes on sales lines) were removed. When new rules are added it is important to analyse the entries generated in the log to see that no excessive logging is performed.

The second consideration  is that the database log should be cleaned up periodically. The log can be backed up to a binary or .csv file if required using AX data  export-import utility and the clean up routine is accessible via Administration>Inquiries>Database log>Clean up log(button).

Keeping one month of logs in the system is probably sufficient especially if they are backed up to the file system.

Please note that running clean up for the first time will take significant time and affect system performance because of the amount of log data accumulated. The task can be batch-scheduled.

The suggested schedule is once per month.

Back up database log:

Create a definition group for database log Administration/Periodic/Data export/Import/Definition group

• CTRL N to create a new definition group.
• Specify a meaningful name and select type Excel to create an Excel file to back up.
• Click OK

• Click on the Table setup button

• Select all the tables and click Delete

• Enter ‘DatabaseLog’ in the table name, Export status = Export to, Specify the file name where the back up must be saved.
• Close form

• To Export file click on the Export to button, click OK button if file should be saved to the path specified in the definition group (Previous example) or click on ‘Override file names specified in definition group’ and specify a different file name.

1.3 Temporary sales order/purchase order processing data

Temporary sales order

This table stores temporary sales order line entries when a sales order is posted. The temporary lines are not deleted after posting and need to be cleaned up periodically. The clean up job can be accessed via

Accounts receivable > Periodic > Clean up > Sales update history clean up.

Delete both executed and errorneous data. There is no reason to keep more than a month’s data. Please note that running for the first time will take significant time and affect system performance because of the amount of log data accumulated. The task can be batch-scheduled.

The suggested schedule is once per month.

Note: there is a similar job in the AP module,

1.4 Temporary master planning data.

Table : InventSumLogtts

This table stores inventory movements data used by dynamic master planning. The table is supposed to be automatically purged when a dynamic master plan is run. If there are test companies in the production environment then it would add up data in this table.

Also there is no periodic job to clean up this table, so typically if a company does not use master planning data must be deleted from the table manually (e.g. using SQL server).

It might be beneficial to check this table immediately after master planning session is run to see if it purges correctly.

1.5 Sales line

This table stores sales lines. There is a periodic job in the system that can either delete or void (move to another table) closed (invoiced or cancelled) sales order lines.  The job is accessible via AR>Periodic> Delete orders.

Depending on the “Mark orders as voided” check box in AR Parameters the sales orders selected in the Delete orders periodic job will either be completely deleted from the system or moved to the voided orders table.

2 Non-production companies in the Production system

Check if the client uses test companies (e.g. TST) in production environment which for example account for around 40% of all data in the inventory settlement table.

It is not recommended to keep test companies in the production environment. If a copy of live data is required it is recommended to restore a live database backup into a separate test environment. The recommendation is to delete non-production companies from the production environment, which would lead to signinficant reduction in table sizes and index sizes.

If there is a requirement to copy a single company only without using the database backup standard dynamics AX import-export utility can be used to achieve this, but the SQL database back up is a faster method.

3 Periodic indexing

Table indexes should be rebuilt periodically to ensure optimum data query performance. Re-indexing should be done periodically and is accessible via Administration>Periodic>SQL administration

Recommended schedule is weekly. Indexing must also be done after any significant update to master file data, e.g. reassigning customers to a new dimension etc.

• Read the complete post at http://shekhardiptiman.spaces.live.com/Blog/cns!8464DE3384E5B978!205.entry