Picking list registration performance

We recently have implemented a new Picking application in our warehouse. Our slow performing statement e-mail has immediately highlighted that with our data volume the WMSOrder and SalesTable records were underperforming, taking 5+ seconds each time when the form was opened (close to 20). We needed to fix the Picking list registration performance.

WMSOrder

Apparently it is a standard AX form and table, but the link which they have been using is incorrectly defined, resulting in an InnerJoin for SalesTable unnecessarily.

Picking list registration performance

Also the field used for the join has no index, you should create one for WMSOrder.InventTransRefId to gain more performance.

After the adjustment it looks much better and performs really fast:

WMSOrder

 

DAXRunBase / 2018-02-27 / AX 2012 / 0 Comments

SQL data compression for Dynamics AX 2012 (Part 2)

It has been a while since I have touched up on SQL data compression in part 1 on my blog. It is time to re-visit the topic.

SQL data compression for Dynamics AX 2012 (Part 1)

Since that post Microsoft has published an excellent series of articles on how to apply compression within AX, or directly on SQL Server. It is a must-read blog post for everyone, so start there:

https://blogs.msdn.microsoft.com/axinthefield/sql-server-data-compression-for-dynamics-ax-0-blog-series/

Since JJ Food Service has a very large database, we have started applying compression to Production AX. Our SalesLine and CustInvoiceTrans has been between 250-350 GB size each! Our approach was to apply compression before a maintenance window using the Enterprise Edition license of SQL Server in ONLINE mode and using TempDB sorting, so it was running during business hours only adding a slight overhead to the system (mostly as disk I/O). It took 4 and 6 hours respectively, and space saving was more than 75% which is excellent.

Once the compression has finished and we were about to begin our regular Friday night maintenance, once the AX AOS, reporting and web services were all down, we could run the post-compression SQL script provided by Microsoft to populate the SQLStorage table. This is required for the Data dictionary synchronization step to recognize compressed indexes, so it would not drop and recreate them uncompressed.

Eventually we will consider compressing our whole database for Page level. The reason being is that Dynamics 365 for Finance and Operations, Enterprise edition in the background uses full page compression for the whole AX database. If you would like to use the upgrade scripts from 2012, even the documentation mentions that compression is a pre-requisite step:

https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/migration-upgrade/prepare-data-upgrade

As a result we have seen great performance boost for the tables involved and much lower numbers in the slow performing SQL statements, since more data could be kept in the SQL memory buffer.

DAXRunBase / 2018-02-27 / AX 2012, Dynamics 365 for Finance and Operations / 0 Comments

Implementing Microsoft Dynamics 365 for Finance and Operations – Book review

It has been very busy times in the past couple of weeks, by doing the preparation steps for our upgrade to Dynamics 365 for Finance and Operations at JJ Food Service. As you can imagine it is not an easy task with an AX system that has been running for 11+ years, upgraded for every major version since 3.0 with a 2TB+ database size. As the Technical architect and with no partner involved I needed to understand all the components, challenges to be expected, processes to be completed in order to carry out a successful move to the cloud. Fortunately I have received an editorial copy of Implementing Microsoft Dynamics 365 for Finance and Operations.

Implementing Microsoft Dynamics 365 for Finance and Operations

Implementing Microsoft Dynamics 365 for Finance and Operations

First of all I really like the idea that the book starts with an introduction of the various Dynamics 365 products and reporting/data management tools, since Microsoft’s intention is to bundle together different applications in the cloud via a subscription-model. This gives a great overview of the available tools. One point to mention here is that CRM has been split up and compartmentalized as different, smaller applications. This is the direction where Dynamics AX is heading as well eventually.

The Implementing Microsoft Dynamics 365 for Finance and Operations book is also touching up on Application Lifecycle Management (ALM), through the utilization of LifeCycle Services (LCS) portal. This is what we use for managing and architecting our environments, going through the project implementation, mapping our business processes, deploying data packages and releasing code.

Since the book is aimed at audience like myself, technical architects with a strong development background, I have found this book to be an excellent resource for getting into the nooks and crannies of using Visual Studio for development, VSTS for version control, walking through the various major changes like ditching AIF for RESTful API, JSON and OData.

I also really like that the book touches up on the automated testing part, which is very much neglected in most implementation projects. I would expect that it would change in the future, forcing customers and partners to provide testing in some form before code could be applied in a Production instance with a certain code coverage threshold met.

All-in-all I found the details very well summarized in the book, and is a great starting point to build essential knowledge for a new AX 7 implementation project. The rest could be filled in by reaching out to the extensive Microsoft Docs site as a detailed technical and user manual, if you need more insight into the various areas.

 

 

DAXRunBase / 2018-02-27 / AX 7, Dynamics 365 for Finance and Operations / 0 Comments