Some might argue Dynamics AX is an opened source ERP. With the developer license, you can see the whole stack of Microsoft code (SYS and SYP layers), and you can overwrite its core business logic to match your specific needs (VAR or CUS layers). That flexibility is maybe the number one strength of the product, but it can also leads into performance issues if the customized code doesn't match the Microsoft Best Practices.
Premier Field Engineering has the great opportunity to see the product running live and the performance troubleshooting often means doing a code review of all layers. We often demonstrate the need to optimise the code with the 10/10/80% application rule:
- 10% of the performance can be explained by infrastructure issues like network, disk latency, memory pressure and high utilisation processors
- 10% by wrong configurations in SQL and AX such as Max degree of Parallelism, database files size and location, AX Database logging and Number Sequences
- Then 80% can be found its root cause in the business logic X++ code in correlation with index tuning and data modelling.
Writing the customization in X++ to match customer’s requirements is one thing, making sure it is well optimized for high volume transactions and large tables is another one. This is where experience comes into play of course, but here is a great tool for you: Trace Parser!
The latest version of the tool has been released with Dynamics AX 2012 R2 and allows you to analyse AX 2012 ETL trace file but also AX 2009
- Trace summary: time spent in the database vs. in the application: this is great to confirm
the 80% rule.
- SQL queries and X++ Call Stack mapping: this is where you can start the troubleshooting and find the root cause of the latency by looking at the
queries order by Total Execution Time for example. Please read the previous post from Tom
Stumpf on the AX tracing.
- And my favourite, Trace Comparison. It can compare two trace in details and help you to validate the code optimization. The first trace is typically the benchmark and the second trace is the new one to be tested.
Finally I will like to remind developers of the importance of the Best Practices tool. Following its standard rules is mandatory and all error and warning should be resolved before code is promoted into production.
The Premier Field Engineer team delivers Dynamics AX Code Review to help customers and partners assess the customization and
remediate most important findings with code optimization. The Code Review also covers the Best Practice results, Performance improvement (SQL operations with Update_RecordSet and Insert_RecordSet, use of aggregate functions, Join instead of If statement, abusive display methods).
Bottom line: if you have performance issue, leverage Trace Parser to find the root cause. And if you don’t have yet any performance issue, validate your new development with Trace Parser before any release.
Note: you can import both AX 2009 TRC and AX 2012 ETL files into the same Trace Parser. Here are the work around:
- for AX 2009: Run Mofcomp ax_trace.mof
- for AX 2012: Copy the two dlls TraceProviderCrimson.dll and TraceProviderCrimson.man
into the Windows\System32 folder and run wevtutil im TraceProviderCrimson.man