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:

https://blogs.msdn.microsoft.com/axinthefield/top-10-issues-discovered-from-dynamics-ax-health-check/.

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.

AX database tuning and maintenance does not stop there, you should not forget about TempDB as well to avoid contention and have high performance, for which you could find excellent details if you look up Brent Ozar’s website. There is even a cheat sheet available to help setting it up correctly.

The next and probably one of the most important bit is that your performance constantly degrades over time when you manipulate data, due to index fragmentation and out of date index/column statistics. Many DBAs choose to use the SQL maintenance script freely available from Ola Hallengren, which is well-documented on his website. Microsoft has some guidelines on this, however I like better control for tweaking what to run myself using parameters.

Our maintenance strategy is to do a weekly index rebuild/reorganize online (removing the offline rebuild option in the script, and this is only possible on an Enterprise Edition SQL Server) with updating modified statistics only using the below settings and script built on Ola’s toolkit. Before running the script you could consider setting Max degree of parallelism to a fixed value (ideally between 4 to 8 depending on your CPU cores, which does help with index rebuild operations), then reset it to 1 afterwards to match OLTP workloads.

EXECUTE [dbo].[IndexOptimize]
 @Databases = 'YourAXDB',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE', -- no offline -> ,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE', -- no offline -> ,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @PageCountLevel = 1,
 @MaxDOP = 0,
 @UpdateStatistics = 'All',
 @OnlyModifiedStatistics = 'Y',
 @SortInTempdb = 'Y',
 @LogToTable = 'Y'

During the weekdays we have identified a handful of tables which are more frequently changing resulting in performance degradation, thus we run a nightly index rebuild/stats update only on those specific tables (namely SalesLine, InventSum, and a couple of custom ones related to machine learning).

Ola’s script is doing an excellent job taking care of it, however it is a single-threaded operation. We have found out that by staggering up the job between SQL Service Brokers the execution time could be considerably reduced for a large-scale maintenance run. Once the script is finalized and ready to use in a Production environment I will publish it in a new post, keep your eyes out for it.

Also in the future I am planning to take a look at how to track and report database growth on a table level, so you could project disk space requirements in advance and consider an archiving strategy using the Intelligent Data Management Framework tool.

In the meanwhile I already have covered some areas which can help with AX database tuning and maintenance tasks under the following posts: