I wanted to write this blog post after a couple of
Dynamics AX Performance Review where the Targeted scenario was execution of the
MRP Batch. Every situation is different but there are similarities as well, especially when it comes to troubleshooting and patterns for improving performance.
The objective of this article is to describe top 10 settings, based on experience from the field, that can help you improve the performance of MRP:
1. Fine tuning using iteration
Because it is often not possible to reproduce the same performance issue in another environment than production, the idea is really to get stable and consistent runs to test every change.
Collect all the configurations and design a change matrix so you can clearly map the impact of each change. This, of course, implies that only one setting is changed at a time.
Each customer will have different constraints, and therefore different goals: onehour maximum every night or 3 hours maximum every week end. It is important to set the right expectation so that analysis can be stopped when the target is reached. Finally, the monitoring should always continue because execution time will evolve due to data composition, growth and business change.
Note: if you want to reproduce the MRP issues in your dedicated Test environment, you should use the same backup of the Dynamics AX Database. Also, the backup should be taken from Production before the MRP is executed so relevant transactional data exists for the MRP execution.
2. Optimize Thread and Bundle
There is no magic to calculate the best number of thread for your instance. I have personally seen MRP running faster when threads were decreased from 32 to 4, and other increased up to 96 threads. Number of threads is defined by the number of AOS Batch server allocated to the Batch group of the MRP execution and the number of threads available per AOS server (System administration - System - Server configuration). By default, there are 8 threads per AOS so with 3 AOS enabled for batch and allocated to the batch group, you have up to 24 threads at your disposal.
Then you can define the number of helpers (threads) in the parameter of the Master Plan (Master planning – Periodic – Master Scheduling – tab Scheduling helpers). The threads created by the batch job will be assigned to all the AOS threads. This means that number of helpers should be less than the number of threads allowed.
Another important factor that can be adjusted is the number of tasks in helper task bundle. Master planning helpers allow independent processes to run in parallel. This results in a substantial reduction in calculation time, since items on the same level in the BOM do not necessarily conflict with each other, and can therefore be distributed across multiple processes.
There is no formula to easily estimate the best number of items (task) in a sequence (bundle). If you have complex Bill of Materials (B.O.M.) structure and some items with a lot of opened requirements, it is better to lower the value. If you have a lot of items with similar demand, it is better to have a higher value. By default, the value is 50 but we have seen MRP running faster with a value of 2 and some other were improved with a value 1000.
You can define the number of tasks in the Master planning parameters (Master planning – Setup – Master planning parameters – group Performance). Here you should also set the Use of cache to the Maximum:
3. Scale up AOS resources
We know Dynamics AX 2012 AOS can scale up more than previous version thanks to its native x64 application. For AOS dedicated to rich users, we typically recommend to have 8 processors and 16 GB of memory per VM and as many AOS as required in the OLTP cluster. But for the AOS dedicated to batch processing and MRP task, it can be worth having more resource on the server. I have seen several customers where the AOS Service consumed up to 50 GB of physical memory during the MRP execution. So it is not always better to increase multi-threading.
So the obvious recommendation is to make sure the AOS server is fully dedicated to the MRP. Also try to run MRP once at a time and outside business hours. It is especially important to make sure no other tasks are requiring locks on tables used by master planning such as InventTrans, InventSumLogTTS and ReqItemTable. You can check the contention on the database access using the SQL job “DYNPERF_Optional_Polling_for_Blocking” from
DynamicsPerf tool:
You can verify the resource available using
Windows Performance monitoring: minimum available memory, working set for Ax32SERV.exe and processor Time.
4. Increase cache if memory allows it
Once you know how much memory is consumed by the AOS process when MRP is running, you can start the tuning of cache settings.
For example, if the minimum memory available is too low, you can assume that memory is already a constraint. If the memory available is stable, then you can increase the level of cache for the specific AOS dedicated to batch. Remember that you can define the following settings for all AOS server or for one specific AOS server.
Go to System Administration > Setup > System > Server Configuration:
- Set Entire Table Cache to 512 KB
- Increase the Global Object Cache to 500,000
- Increase Record Cache limit for Table type transaction to 100,000
- Make sure all transactional tables belong to the transactional Table group
Go to Master Planning > Setup > Master planning parameters: Set Use of cache to Maximum
Note: you can manually take a dump to analyze the memory in case of high memory consumption.
5. Enable Windows Event Tracing on AOS
You should not collect traces for the long process like MRP because it can easily create very large file, easily more than 10 GB for several minutes. The strategy is to schedule the trace to be collected every 20 minutes for about 3 minutes. After importing the traces into Trace Parser, you can analyse which X++ code or SQL queries need to be improved during each phase of the MRP run:
- Do not create missing index without checking the existing ones. Often, performance is degraded when too many indexes exist on the same table. Plus, most of them will not be used because they are already covered anyway.
- We recommend to remove unused indexes when statistics have good lifetime (more than 6 months).
To use the
Event Tracing for Windows (ETW) with the normal Microsoft Dynamics AX tracing providers, go to Performance Monitoring > Data Collector Set > Event Trace data.
When doing iteration, please also rename the Task Description so you can always remember which setting has been changed per run, such as: PROD_MRP_12012015_2300.etl. To collect the trace for the event MRP, you need to configure it using Performance Monitoring: After the trace is imported in Trace Parser, you can verify how many threads are running and compare them in details:
6. Check out index optimization
Like any long running Dynamics AX process, the best way to troubleshoot it is to collect traces using Windows Performance Monitoring and analyse them with Trace Parser:
- You can then find out the expensive SQL queries, by logical read or average time.
- Replay the query in SQL Server Management Studio to visualize the Query Plan
- Use Dynamics Perf to investigate missing indexes on that table
- Performance Analyser for Microsoft Dynamics
- Test new index by replaying the same query
Finally, make sure the index maintenance is enabled and run index maintenance job before MRP. But please make sure no extensive SQL maintenance task is running at the same time as MRP.
Note: you can overwrite SQL parameters like Max Degree of Parallelism if there are only batch processing at night. However when changing MaxDop on the SQL instance level, the plan cache gets invalid.
7. Review session log
First you should look into the session log every MRP execution. Go to Master planning > Setup > Plan > Master plans > Session log > Statistics:
- “Update” should not take long time, however it is singled threaded.
- “Copy plan” and “Auto Firming” should not take long time
- “Auto firming” should be less that coverage
- Coverage should be the long running relatively
- Action and Future message can take long depending on data and number of BOMs
This is the data from Contoso Company:
Then, you can enable the “Track Item task duration” during the MRP run to collect more details about the items and the tasks. It can help you to find the bottleneck, like which item is taking most of the time to optimize the task per bundle value. For exampe, you can check if long running items are assigned to the same bundle.
Go to Master planning > Periodic > Master Scheduling> Master plan:
Note: Go to Master Planning > Inquiries > Processes > Unfinished Scheduling Processes > Inquiries and Process Task Duration. However you can only see the details of every tasks during the execution of MRP.
8. Verify MRP parameters
Most of the following
settings are recommended to avoid additional tasks that will increase master planning runtime by creating unnecessary planned ordered and by calculating optional information (however the business needs to validate any change because it will change the MRP results).
- Use dynamic negative days parameter should be checked: The negative days setting is connected to the lead time of the items. If the negative days are less than the item's lead time, unnecessary planned orders may be created.
- Positive days parameter on the coverage group should not be 0 or empty. If the positive days parameter on the coverage group is 0 or empty, then inventory will not be taken into account for planning and new planned orders will be created.
- If action messages are not analysed and applied on a regular basis, you may want to disable the calculation by making sure the Action message time fence is 0 and disabling the Action message in the Coverage Group.
- If you are not planning BOMs or if propagating delays from supply to demand during planning is not needed for you, consider disabling futures calculation during MRP, by making sure the Futures time fence is 0 and Futures setting is disabled.
- If you are not using Auto firming, override the time fence on your Master Plan to avoid auto firm setup defined in the Coverage plan.
Go to Master Planning > Setup > Coverage Group. Note: you can also overwritte the settgins in the Master plan (Master planning - Setup - Plans - Master plans)
Advise: if you notice many error and warning messages in the session log of the MRP, we strongly recommend you to first run the Consistency Check and then to look at the messages as they can slow down the execution of the MRP.
9. Tune Application configuration
As many processes within the Dynamics AX application, there are several configurations that can have performance overhead when not properly set up. This is especially true for batch jobs where volume of transaction can be quite high. In this case, the contention you may have in regular business hours on one number sequence can become critical during the execution of the MRP.
- Make sure there is no database logging enabled for the transactional tables involved in the MRP. Tracking Create-Update-Delete events can slow down any bulk SQL operations such as Insert_RecordSet and Update_RecordSet.
- Disable alerts as well because they will not be relevant during the execution of MRP.
- Consider Non Continuous Number sequence for all the ID involved during the MRP execution and enable Pre Allocation to facilitate the release of IDs. You can find the number sequences in Master planning > Setup > Master planning parameters > number Sequences tab
- Turn off unnecessary Configuration keys that are not actively used. For example, Process Industries functionality, Retail or Public Sector.
- Disable Context Info on AOS dedicated to batch Processing
Advise: you can use the tool DynamicsPerf and capture the statistics before and after the MRP execution to analyse the table growth and number sequences consumption.
10. Clean Up data
Standard tables that should be clean up on regular basis, for example customer may define a 3 months’ retention policy. You can also use DynamicsPerf tool and the Benchmark query to check the table growth during MRP. When not in production, you can have a more aggressive approach and delete all the data.
- BATCHHISTORY
- BATCHJOBHISTORY
- BATCHCONSTRAINTSHISTORY
- BATCHJOBALERTS
Also specific MRP temporary tables can be clean up:
- ReqTrans
- reqTransCov
- reqPo
- reqLog
- REQPROCESSITEM
- REQPROCESSLIST
- REQPROCESSTHREADLIST
- REQPROCESSTRANSFILTER
- reqRoute
- INVENTSUMLOGTTS
- Reqcalctask
- Reqcalctasksbundle
- Requnscheduledorders
- Reqcalctasktrace
- Reqprocesslist
- Reqprocessthreadlist
- reqprocesstransfilter
All data related to inactive plan versions can be safely cleaned up. The data in the following tables will automatically be cleaned up by the subsequent MRP run.
- Reqtrans
- Reqtranscov
- Reqpo
- Reqroute
- Reqroutejob
- wrkctrcapres
Note: Table InventSumLogTTS should only be deleted when full MRP plan is executed. It should not be clean up when MRP is run against subset of item and with Net Change option or when Capable To Promise (CTP) is used.
Finally, remember to look at all recent Hot Fixes available on Lifecycle Services with the keyword ‘MRP’ or any object starting with 'REQ' like Classes\ReqCalc or Tables\ReqTrans.
Regards,
@BertrandCaillet
Principal Premier Field Engineer
Original URL:
