Top 10 issues discovered from Dynamics AX Health Check

A very informative blog post by Bertrand Caillet covering important points for Dynamics AX best practices for health check, and it contains a sub links under main points. I am glade to share it over my page.

1. Max Degree of Parallelism is not set to 1

2. No Pre-Allocation for Non Continuous Number Sequences with high consumption

3. Insufficient Auto Growth for Data and Log files

4. Processor bottleneck for Dynamics AX batch processing

5. Heavy Application database logging

6. Missing Clustered indexes

7. Wrong Index maintenance

8. Enabled debug in Production

9. Power Management set to Balanced

10. Kernel and Application are outdated and should be patched

The Premier Field Engineer team has been running several hundred of Dynamics AX Health Check worldwide and I thought it would be interested to share the most common issues discovered during onsite. Some of the following issues might look trivial but the reality is that despite all the literature that exists today on the solution Dynamics AX, we can still see same repeated patterns, especially when default settings are used in production. Please note there is no logical order in this list.

1. Max Degree of Parallelism is not set to 1

The default setting from SQL Server installation is zero. If OLAP applications are running on the same SQL Server instance, the risk is that all CPUs are consumed (% Processor Time) and therefore impact performance of the OLTP application. To prevent this, we recommend to set MAXDOP to 1. Please note that in some situation when SQL Server batches or queries are run, using some degree of query parallelism can be beneficial. In this case setting Max degree of parallelism to 2 or 4 is probably a better value to take into account both types of queries.

2. No Pre-Allocation for Non Continuous Number Sequences with high consumption

This is a crucial setting in the Dynamics AX application that needs to be reviewed every few months on production to match the usage of the number sequences. Basically, number sequences can be Continuous or Non Continuous. When they are non-continuous, you can allow pre allocation per ID and therefore reduce the database calls and improve performance. When consumption is high, like several ID per seconds, we have noticed Lock Escalation events on the table NumberSequenceTable. This is especially true when Dynamics AX batch are running and generate thousands of records for Journals Lines creation or Sales Order Invoicing. Please read this blog post to better estimate this consumption.

3. Insufficient Auto Growth for Data and Log files

The default Auto growth is By 1 MB for Data and By 10% for Log files. If the initial size is too small, you will notice frequent auto growth events, resulting in negative performance impact. In the SQL trace, you can monitor such event with ID and see the duration of each auto growth event. The goal is to set an appropriate Auto Growth value on the Dynamics AX and TempDB Data and Log files to prevent frequent auto growth events during daytime. You can monitor such events and their duration in milliseconds in the SQL Trace with ID 92 and 93. One recommendation is to set the Auto Growth with a greater value, from 200MB to 500MB.

4. Processor bottleneck for Dynamics AX batch processing

Most of the customer running Dynamics AX are enabling only one AOS instance for the batch processing and several AOS dedicated for rich clients load which therefore limit the number of batch thread to the number of logical processor available on that single AOS instance. Also the setting on the AOS batch is often the default one with 8 maximum batch thread and 24 hours a day schedule . A good ratio to calculate the number of thread is to multiply the number of cores by 2, but this depends on the processes running and should be validated in testing. To overcome this limitation, you can set different batch thread for different time of the day and enable more AOS for Batch processing at night. For example, you can dedicate one AOS for Rich client load from 8 am to 6 pm and leverage it for batch processing at night. You can read this blog post to learn how to tune the number of thread required for faster batch execution.

5. Heavy Application database logging

This is another important setting in the Dynamics AX application that often has negative impact when over utilized. This feature allows to track all CRUD operations (including Rename primary key) on any field and any table of the database. This information will be stored in the table SYSDATABASELOG. You should not use this feature to track automated transactions run in batch jobs. You can monitor the table growth of SYSDATABASELOG and estimate the most expensive table set up with database logging. Please also notice that enabling the logging on field like CreatedDateTime or ModifiedDateTime will change the default behavior of InsertRecordSet from a single round trip to the database to a record-by-record operation. The three recommendations are:

• Define a retention policy to only keep the latest 3 months for example so that size of table SYSDATABASELOG remains small. You can find the clean-up form under Administration | Inquiries | Database log | Clean up log.
• Avoid heavy logging for transactional tables processed during the batch and prioritize activity that is absolutely necessary either for legal compliance or to meet other business requirements.
• Avoid logging the columns CreatedDateTime or ModifiedDateTime of any table.

6. Missing Clustered indexes

This might not be the most common issues but it is definitively one of the most impacting one. All tables in an OLTP database should have clustered indexes, and you should investigate them by looking at the highly active tables, tables frequently involved in blocking or deadlock situations, tables that frequently incur the overhead of forwarded rows. Because the database model is changing during the lifecycle of the application with new customization and because indexes are managed from the Application Object Tree, it is important to periodically check the missing indexes from SQL Server Statistics. You can use the query “4-Analyze_SQL_Indexes.sql” from Dynamicsperf version 1.1.6 to find all missing indexes.

7. Wrong Index maintenance

Following the Missing clustered indexes issue mentioned above, the index maintenance is clearly one of the unhealthiest issue for Dynamics AX. Having a bad index maintenance or no index maintenance at all will have the same consequences: when statistics are outdated, Query Plan will use SCAN instead of SEEK operations and their performance will be badly impacted. The level of fragmentation is not that critical as such for performance, but it should be part of the weekly maintenance. The following is a recommended example:

• Reorganize indexes that are larger than 1000 pages and are between 10% and 30% fragmented.
• Rebuild indexes that are larger than 1000 pages and more than 30% fragmented using a fill factor between 85% and 95% depending on the frequency of the job execution.

It is also strongly recommended to run Update Statistics regularly with FULL SCAN, or with at least a 50% sample, as well as having Auto_Create_Stats and Auto_Update_Stats enabled. If you are running SQL 2008 R2 SP1 or greater, you can also enable Trace Flag 2371.

8. Enabled debug in Production

In the Dynamics AX Server Configuration Utility, you should always disable the two settings that allow user breakpoint and global breakpoint to debug X++ code. Even though you delete all breakpoints in the Application Object Tree, you will still suffer from a clear performance degradation around 10%.

9. Power Management set to Balanced

This is an easy one but almost never implemented in production since the default power management is set to balance. Changing it to High performance is highly recommended for all Windows Server 2008 used in Production. You can see this setting from Control Panel – hardware – Power Options or with the following command: “powercfg –getactivescheme”.

10. Kernel and Application are outdated and should be patched

Last but not least, maintain your Dynamics AX solution as much updated as possible. For example, you can notice that latest Rollup for Dynamics AX 2009 SP1 is RU8 and is already one year old. Several hundred of Hot Fixes have been released since and it is a best practice to apply those to leverage the latest fixes from the Product Group. The two recommendations are:

• Patch the Binaries with the latest Kernel available. You can see the latest KB on the AX Support Blog. As of today, the latest one for Dynamics AX 2009 SP1 is build 5.0.1600.1824 from June 2013.
• For the Application, you should upgrade to latest RU available, but you can also proactively check the list of individual Hot Fixes relevant to your business logic.

As you can see this list is not exhaustive and we could elaborate hundred of issues discovered from existing Dynamics AX live instance, but I do hope it can help you remediate some of the current issues you may experience. I will also encourage you to read the great article written by Arvind Shyamsundar on MSPFE blog regarding the 10 top SQL Server Issues uncovered by the SQL Server Risk Assessment Program.

Finally, please contact your Microsoft Premier Technical Account Manager if you will like to receive guidance from the Premier Field Engineering.