SQL

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

 

By |2018-02-27T10:22:18+01:00February 27th, 2018|Categories: AX 2012|Tags: , , , , , |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.