SQL Archives - Page 3 of 5

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 DAXRunBase|2018-02-27T10:22:18+01:00February 27th, 2018|Categories: AX 2012|Tags: AX 2012, performance, SQL, statements, tables, X++|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.

By DAXRunBase|2018-02-27T09:45:41+01:00February 27th, 2018|Categories: AX 2012, Dynamics 365 for Finance and Operations|Tags: AX 7, compression, Dynamics 365, migration, performance, SQL|0 Comments

Fix Table and Field ID synchronization conflicts in AX

It is a very common issue when we change metadata in a source/build environment incorrectly that the Production environment’s AX database field identifiers are getting out of synch from our Application Object Tree (AOT). This can typically happen during a code upgrade scenario (after applying a hotfix or a new ISV solution), or if a field/table has been deleted (because for some reason we did not want to rely on the SysDeletedObjects6X configuration key: https://technet.microsoft.com/en-us/library/dn793968.aspx). In this post I would like to share my latest code to Fix Table and Field ID synchronization conflicts in AX.

The kernel is storing your current data DB’s object identifiers from your AOT (your ModelStore in the _model DB) in a table called SQLDictionary. When you do metadata object changes and release the ModelStore to your other environments, AX allocates new Object Identifiers sequentially per TreeNode type. For example the Tables are from the same range of IDs, but the Fields subnode of the Tables are starting from the same sequence for each table. You can verify this in SQLDictionary by checking TabId and FieldId values for them.

Before you attempt to Synchronize your Data Dictionary after the ModelStore release, the first step should be to run an ID conflict fixing tool. Once the IDs in the SQL Dictionary are matched with your AOT, it is safe to start synchronization. You get a visual feedback on the ID fixing tool’s progress as per below, and it finishes relatively fast:

You may read up about the issue in a bit more on the following link and have a look at how others were approaching the problem.

http://daxdude.blogspot.com/2012/12/table-ids-out-of-sync-remediesprevention.html

The Fix Table and Field ID synchronization conflicts in AX job and the class behind is available on the DAXRunBase GitHub:

https://github.com/DAXRunBase/AX-2012-R3/tree/master/Fix%20Table%20and%20Field%20ID

By DAXRunBase|2017-06-26T15:53:42+02:00June 26th, 2017|Categories: AX 2012|Tags: AX 2012, metadata, ModelStore, SQL, SQLDictionary, synchronize, tables, X++|0 Comments

Do not update AX database tables from SQL

This requirement keeps popping up on the Community Forums by people who are not familiar of how Microsoft Dynamics AX works, typically for third-party integration, data warehousing and reporting scenarios. In this post I would like to give some insights on why is it not recommended to update AX database tables from SQL.

Record identifiers

Each row in Microsoft Dynamics AX database within Microsoft SQL Server has a unique identifier which is by default the primary/surrogate key as well for the records. The field is called RecId, and it is maintained as an incremental, int64-based positive or negative number. The highest number I was able to allocate is 3 below the maximum value of a 64-bit integer, which was 9,223,372,036,854,775,805 and you would get an error for the next value:

If you would ever reach the maximum value, the manual fix is to set the next value to be -9,223,372,036,854,775,807.

(more…)

By DAXRunBase|2018-09-11T11:51:54+02:00June 13th, 2017|Categories: AX 2012|Tags: AOT, AX 2012, cache, DIXF, metadata, RecId, SQL, statements, tables, X++|0 Comments

Working with Entity Store and DIXF in AX

There is an excellent tool available for advanced reporting and BI capabilities that partners and customers should start exploring. In this post I would like to show how to work with Entity Store and DIXF in AX, which has been published shortly after the Technical Conference held in Seattle last year: https://www.daxrunbase.com/2016/02/24/technical-conference-day-1/

Working with the Entity Store and DIXF in AX takes some time to set up, but once it is running, it is acting as an up-to-date Data Warehouse for you, where you could utilize advanced features such as Clustered Columnstore Indexes, and make truly amazing visualizations which will please the eyes of your managers whom loves KPIs, and will make your teams more productive by knowing what to look for.

You can find the Public PowerBI built for the stock and items at JJ Food Service as an example of what could be achieved for visualizations built on data that is coming out of your AX environment:

(more…)

By DAXRunBase|2017-06-07T11:36:34+02:00June 7th, 2017|Categories: AX 2012|Tags: AX 2012, Azure, DIXF, Entity Store, fix, kernel, SQL, X++, XPO|4 Comments