My world was rocked when I learned that my precious General Ledger forms were taking forever to open up for a client. I take a lot of pride in the way that I customize and report on a ledger so to hear of some slow performance problems was very concerning. Users reported waiting minutes for the Trial Balance and other forms to respond. The AX SQL DBA Team and the Infrastructure team reported seeing nothing in terms of system metric performance to correlate with the user slowdowns. The case was perplexing. Lots of things can cause a GL to start acting up. This seemed challenging. Yet, another case in my career of an AX problem that seems to look undetectable.
Part 1: Talk to People
In my first interview with users, they told me that they system was going to ‘hell’ and that everything was slow. I chronicled what occurred when they noticed the performance issues. From my discussions with users, I noticed a repeating pattern of time. Performance wasn’t always slow but sometimes slow. It tended to really show up around the end of the month and after code moves. End of the month, I knew, from experience is when users tend to do all the heavy closings and run the biggest reports. You’ll typically see SQL locking patterns here. After code moves was interesting.. What was going on that could be causing the code moves.
Part 2: Troubleshoot the End of the Month Slow Down
I knew that something was going on at the end of the month, but I didn’t know what was going on. However, since it is usually concurrency related, you check locks. The first thing that I noticed was unusual CXPacket waits. These tend to come from AX being configured without it’s specific needs in mind. Regular SQL databases don’t use a Maxdop of 1. With AX, however, you have extensive use of transactional cursors. A Maxdop of 1 is essential or you end up with parallel locking problems. In this case, this was set to unlimited – an absolute killer for AX daily operations that will result in several parallelism problems. I had the client change that and there was an instant improvement in performance.
PART 3: Slow Down and Freezing after the Code Moves
I started a trace from the client here because the server showed nothing in and of itself to dictate what the users were experiencing with the slowdown. So, I knew that an AX trace in and of itself would not tell the answer here. This was something else. I brought out process monitor and begin to watch the live client events. What I found was that when code is updated it forces a sync with the users Roaming Profiles. In this case, the users had very slow Roaming Profile performance. The net-net was that AX also went slow because it was unable to update them. Now, that I knew where the problem was, I needed to figure out why the user profile performance was so slow.
I noticed that the AOS ran for 245 seconds when trying to download the updated dll’s to the user profile location:
Interesting.. I understood that the client was running slow and that it was rooted in the Roaming Profile performance. This was leading to the application hangs as the client had to wait for updates to cache and .dll’s. Upong looking more closely at the problematic wait, I found an antivirus leading to severe disk performance on the client cache locations.
Had it. I could see now what was leading to the diminished disk performance. I gave the client this information along with some recommendations – faster disk for roaming profiles on the AX machines, excluding the AX client locations from the antivirus scan, and some general best practices for Roaming profiles in General. Issue solved. While the client did not have a chance to implement all of my recommendations, the GL performance greatly increased just by changing the Maxdop. To minimize the effects of the slow user profiles, the client began to try and limit events which typically lead to user profile syncing such as incremental code compiles or full ones, activating inbound ports, changing security, ect. Things ran better from there with the client having a full understanding of the importance of roaming profile performance when it comes to AX.