2012 Master Planning Floods Database Log of InventTable

When using Database log for table InventTable, i.e. Released products, Master planning can flood the Database log with Update events where Bills of materials are used.  Master planning recalculates the BomLevel field on InventTable in order to process items in the proper order, and it does this first by setting it to 0 and then later to its calculated value, thus producing 2 update events in the Database log for potentially many InventTable records.  It does this even when the final calculated value for BomLevel is unchanged from the previous value.

To resolve this, use the .skipDatabaseLog(true) and .skipEvents(true) methods for the InventTable table buffer in the BOMLevelCalc class.

Master Planning Performance - the Statistics Solution

Since upgrading to Dynamics AX 2012 R3 CU8 in May of 2015, the performance of our multi-company master planning runs at night has been wildly sporadic.  In the best case, the total run time across all companies would average 200 plus/minus 10 minutes or so.  Frequently one company's master planning execution would spike from 15 to 45 or even 60 minutes.  This tended to happen in manufacturing companies, and it seemed more likely to happen in master plans using sales forecasting.

Here's a quick graph of total minutes of execution over the last 90 days or so.




Along the way, we tried upgrading from SQL Server 2012 to SQL Server 2014, and further to SQL Server 2014 SP1 CU3 and finally CU4, to no avail.  In some cases, a strategic DBCC FREEPROCCACHE would correct poorly compiled queries, but not always, and not reliably.

The solution turned out to be a problem with the STATISTICS of the master planning tables.  Since a master plan regeneration can effectively delete and re-insert virtually every record in these tables, it makes some sense that the statistics on these tables could get out of whack so easily and regularly.  Note that we were already using trace flag 2371, and enabled the PARTITION and DATAAREAID literals in our environment immediately after upgrading to 2012 R3.

What worked for us was to setup a SQL Server Agent job to execute the following SQL every 5 minutes throughout the duration of the master planning execution.  All together they take about 15 seconds at our scale.

UPDATE STATISTICS dbo.REQPLANVERSION;
UPDATE STATISTICS dbo.REQPO;
UPDATE STATISTICS dbo.REQROUTE;
UPDATE STATISTICS dbo.REQTRANS;
UPDATE STATISTICS dbo.REQTRANSCOV;
UPDATE STATISTICS dbo.REQUNSCHEDULEDORDERS;

To give some idea of the scale and distribution of our data, here's a record count across some of our companies for the REQTRANS table, sorted from highest to lowest.  It seems like an ideal case for STATISTICS to matter.

[Number of records]
546852
153113
123262
114029
89341
70635
63133
62812
41938
33191
18693
17601
8307
4098
4051
3077
1409
1383
1290
6