Join the Microsoft Dynamics AX Community at https://community.dynamics.com/ax/ today!

Tuesday, January 19, 2016

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

2 comments:

  1. We have the same weird AX performance issues. We have bad query plans appearing in SQL server. Interestingly we turned on an agent like you described above and it made the problem worse - immediately after the agent was started bad query plans appeared and the system grinds to a halt.

    Any thoughts welcome.

    ReplyDelete