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:

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:

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!