Dynamics AX Performance Tuning (2012 R2)
02 May 2014
Whenever you implement an ERP your primary focus is always towards the system’s functional ability; making it compatible with your business, customizations, reporting requirements, etc. In this exercise some areas often get overlooked that by themselves are extremely important.
One of these areas is performance, which is a vital area that should be considered from start to finish in any ERP implementation. By overlooking ERP performance you run the risk of turning your project into a complete failure. Since without solid performance, your implemented ERP isn’t going be of much use, especially because as an ERP grows its overall performance gradually declines, which makes it less effective.
Dynamics AX 2012 R2 provides a number of ways via which you can analyze and improve ERP performance. In this post we’ll look at some of the methods for Dynamics AX performance tuning (for Dynamics AX 2012 R2), via which you can achieve a significant gain in performance.
The performance improvement methods offered by Dynamics AX 2012 R2 can be divided into two main categories:
- X++ or Code Tuning
- SQL tuning
Let’s look at these in detail.
X++ or Code Tuning
The first performance improvement method Dynamics AX 2012 R2 offers is Code Tuning. Since in order to improve application performance the first thing you need to look at is the application’s code and the various factors via which you can tweak this code to help you improve application response time. The following is a list of Dynamics AX performance tuning methods or rather best practices, that you should follow in order to make your code more robust:
- Use set based operations
- Set based operations are operations that are performed on bulk data in Dynamics AX. For example: inser_recordset, update_recordset, delete_from etc.
- Along with using set based operations, wherever possible you should use aggregate operations instead of using iterations.
- Avoid using loops if you only have to check certain conditions.
- Bundle up calls from the client to the server. This will improve overall performance and decrease latency.
- Don’t use JOINs on memory temp data, as this will only increase the overall time of data retrieval.
- Where ever possible, try using parallel processing.
- A Call that does not require to be looped again should be moved out of it.
SQL methods for Dynamics AX Performance Tuning
The other performance improvement method that Dynamics AX 2012 R2 offers is SQL tuning. SQL tuning is also one the key areas that should be looked at, if you want your application to be robust and scalable. In our experience, we’ve observed that if SQL tuning is performed regularly, it really helps improve application performance, even in non ERP systems.
The following is a list of SQL methods for Dynamics AX Performance Tuning (2012 R2).
- Set SQL Max Degree parallelism with proper values
- SQL query optimization
- Have a clustered index on every table and ensure that all your queries are index supported
- Do not create indexes directly on SQL use AOS
- Evaluate compression to reduce IO footprint
- Use Sorting with correct indexing
And that’s it.
Interested in Microsoft Dynamics AX Consultation? Folio3 specializes in development, integration, upgrades and customization of Dynamics AX 2012.