Utilize Indexes to Enhance Performance in Microsoft Dynamics AX 2012
Posted on: February 13, 2017 | By: Jarrod Kraemer | Microsoft Dynamics AX/365
Authored by: Craig Leska – Senior Technical Consultant
Microsoft Dynamics AX 2012 is a dynamic system, pun intended. It is not a set-it-and-forget-it ERP system. Over time, as your data grows, you need to monitor your system to fit your company's growth and needs.
What you do today is not necessary what you will be doing tomorrow. As users become more and more comfortable with the system, they use it in a different way than whey they were first trained.
I recently had a client who was filtering on a list page and then extracting data from the largest tables in the system. The data was filtered to the list page in very little time and they were able to scroll through without any issues. Contrarily, when they would do a CTRL+T to export to Excel, it took almost 4 hours and locked the session up while the export was in process. The user would occasionally kill their session and start another one. We know what happens then, the query continues to run on the backend and then the user starts another export only compounding the issue.
Upon going through the code and running the SQL server profiler, I determined what additional indexes were needed and added them. After the indexes were added, the export Excel took only a matter of seconds! This was a table with 30+ million records and we were sending roughly 42,000 records to Excel. Clearly it was a vast improvement with the addition of a few indexes.
Performance tuning has always been a tedious task; there are many factors and many variables thrown into the mix. Luckily there are many tools at your disposal, such as Dynamics Performance Analyzer, SQL Server profiler, performance monitor and AX trace parser, to name a few.
In addition, you need to review your SQL server settings. Microsoft has a list of recommended trace flags and settings to optimize your database.
In my experience, usually it is not simply one change which will make a big impact in system-wide performance, but rather a bunch of little tweaks here and there, and in all the right places, that will provide results.
Also, remember to keep up-to-date on your database maintenance plans. For additional information please feel free to reach out to us at info@loganconsulting.com or (312) 345-8817.
All the best!
Logan Consulting
www.loganconsulting.com