I believe and preach best practices when it comes to performance tuning… However, sometimes, the fire is hot and we are experiencing some extreme pressure to get performance done quickly. So, we have to get practical fast and do something to help out quickly while buying time to truly fix the implementation. In this series, none of these fixes are permanent fixes but they sure can buy us some relief and time.

Disclaimer: At Present, in Dynamics 365 Operations, you must call premier support and give them 8 hours to do any of these changes due to the current ban on being able to do anything in production. Yes, this is extremely unpopular, but this isn’t the subject of my blog post.

Scenario… You start to get locking on the Inventory tables that are used for calculations or the Parm Tables or some other type of worker table. And of course, when it happens, the entire system performance just goes down. You have to understand the reason or the locks. It means that something is holding on to the tables for too long. But here is the issue, AX uses some outdated SQL which doesn’t always agree with the query analyzer built into SQL Server. This causes the sq. to get a little too aggressive at times and lock up an entire table – especially when the tables barely have any rows and a medium size number of rows are being transferred. If you fear that you are suffering from this mechanism and see excessive locking on worker type tables, this line of code on the worker table can often be a gift from heaven.

Here are just use the Alter Table.. lock_Escalation=Disable on the InventSumDelta table – a common cause for slowing down entire implementations.

And Walla.. You still get locking so you don’t have to worry about data integrity. But now that one irritating process is stopping everyone and causing AX to run slow for the entire implementation. Never ever completely turn off locks as that will cause data integrity problems. What this does is stop the escalation of locks at the cost of more RAM. While it isn’t perfect, buying time is the name of the game and this can buy you a good amount of it to take care of business. We’ll look at another strategy in the next post.