We dive into the world of advanced Dynamics AX support cases and find a company which had 755 million reasons for some extreme slowness around warehousing functionality and inventory on-hand. It’s a very common temporary obstacle for big companies to have to adjust to the demands of performance tuning high size, high volume environments. But in this case, there was a special twist leading to seven hundred and fifty-five million reasons for pain. And as another case, this was a reader of my blog. Readers of my blog usually contact me with more advanced performance tuning cases than normal because they know the skillset and have read up on the standard approaches. Let’s go into it.
And it looked like such a Normal Case of Performance Dynamics AX Support until..
A famous big retail company with Dynamics AX came to me with a case of some slowness around warehousing. Inventory On hand was taking 10 to 20 minutes to run and the system was often locking up. There was slowness around the release to warehouse form and the load planning chassis. As usual, license plates were enabled, which is usually the chief cause of the initial problems with a D365 or Dynamics AX slow warehousing performance case. There was lots of custom code and some of it needed work, but it wasn’t too bad. But there is a special twist. Just wait a bit to cover something fundamental.
Previously, I had blogged and discussed the cases of Parameter Sniffing.
So, I spring into action with the various options for fixes
The steps go like this when a customer has a massive warehousing performance issue:
|Fix||Difficulty||How long they last||Dynamics AX Support Advantages||Disadvantages||How Common|
|Buy Bigger Hardware||Super Easy||A few months||Sometimes, it really is the hardware||Gets very expensive and often misdiagnosed||Very Common|
|Inventory Dimension Cleanup||Super Easy||About a year on average but a continued strategy||Quick and Easy. Adds some minor archiving and slows down overall size growth||Over time, size grows back because all transactions are not removed||Very common|
|Plan guides||Easy||Temporary fix||Quick and Easy like the dark side of the force, but can work miracles when you need help fast||Only temporary, requires extremely high maintenance, and does not last||Common|
|Indexes||Easy to Hard||Can be a long time||Carefully planned, can yield big dividends||Often abused with overuse||Very Common|
|Tuning Code||Hard||Can be forever||Fixes it right||Fixes can take a long time and can be tremendously complicated to fix||Very Rare|
You will notice that tuning code is the rarest and hardest of the performance fixes which is why most Dynamics AX support cases avoid it.
So, after presenting the options, the company chose the following choices:
|Buy Bigger Hadware||Yes, but before I got there||Recommended by other Performance Tuners, brought time|
|Inventory Dimension Cleanup||Yes, but after I got there||Recommended by Everybody|
|Plan Guides||No||Quick fix. Would prefer something more permanent|
|Tuning Code||Yes,this was the preference||They had hired other performance tuners, but wanted to see what I could do with the more advanced cases|
Surprise, Surprise, my client had chosen the most advanced options for Performance Tuning
So, the client asked me if I could get the code all squared away. Again, this is very normal when clients have been through multiple performance tuning organizations. They quickly become trained on their issues and get a very good indication of where the difficulty is at. They won’t follow a standard approach to performance tuning because they’ve often been through one multiple times. Instead, they are hunting for a different form of Dynamics AX Support.
So, we start the performance fixing with inventonhand
I go to action on inventonhand, tuning it from 15 minutes to one second with some code fixing. I also put a fix in that would stop the parameter sniffing issues for onhand lookups that often accompany major manufacturing companies. Meanwhile, in another environment, the client ran the inventonhand cleanup job. Everything seemed fine and was great.
The inventonhand cleanup job had also ran well. Now it was time to combine the two, do some testing, and call it day.
But something happened to the system after the inventory dimension cleanup job
Suddenly, I was notified that the same job that ran in one second was now timing out. Interestingly, after the inventory dimension cleanup job ran, SQL went wacky. It’s estimated row count went up to seven hundred and thirty five million.
Wait a minute, the inventory dimension cleanup job is supposed to actually help with performance. What happened?
Previously, I had said that Size was our biggest challenge, but why would decreasing the size cause so many problems as that is what the inventory dimension cleanup job does.
Different versions, even service packs and updates, behave different with SQL server. This is one of the main reasons why certain versions of SQL are not supported. This client was running one of the SQL 2014 versions. The inventory dimension cleanup job reduced the row proportions in such as a way to cause the sql algorithm to go crazy. It’s now produced a query plan estimating seven hundred and thirty five million rows when there are only 16,000 rows that it actually needs.
In this case, I tweaked the code for the algorithm. I didn’t feel like trying to install 10 versions of SQL until one of them worked. This was much faster. Now watch what happens with the estimate. The total was 16,000 rows (not shown), but the estimated amount is 15,000 rows. This is a much more accurate estimate. The query plan corrected itself and the time went back down to one second. Issue Resolved.
Dynamics AX Support Issue Solved but what did we learn
We saw something very unusual. We saw where a decrease in size actually caused the sql query plan to wacky due to the way that the query was designed out of the box. Usually the inventory dimension job just works, but sometimes, we just don’t know. This is one of the chief reasons why Microsoft puts a disclaimer on nearly all the performance tuning including inventory dimension cleanup. You can never say how a particular system reacts to performance. Another system would have been just fine with another version of SQL or a different makeup in the inventdim table.
One good resource that you can use, which is good to look at for compatibilities with various versions of ax is the Dynamics AX Support Version Information page. Here you can often find supported SQL versions and that helps a lot. You can also find some information to make sure that your Dynamics AX Version is supported here.
That’s all folks.. Till the next time!