AX database tuning and maintenance – How to keep it healthy
It is very crucial for your environment to audit, evaluate, then carry out an AX database tuning and maintenance task regularly in order to keep Microsoft Dynamics AX in a good shape.
There are many areas which are frequently being overlooked by partners and customers, for which Bertrand from the Microsoft PFE team has written an excellent summary that you should read here:
Many of these configurations are very important to keep your environment healthy from day 1. Often overlooked settings include for example the Index fill factor. It causes fragmentation as records are getting created and updated in your database, resulting in slower statement execution times and even timeouts, which is very bad and has been discussed in this post:
If you’ve got very large DBs like we have, you could be safe with setting 90% then doing an index rebuild to not bloat your DB size by too much. Other than that the MS recommendation is between 80-95%.
Having the correct SQL Trace Flags are also equally important, you could read about them again on the PFE blog. We are running 1117, 1118, 1224, 2371 and 4199. Also it is recommended to use the dataAreaIdLiteral AX setting if you have a multi-company environment, to avoid parameter sniffing.