You’re offline. This is a read only version of the page.
Announcements
110
We are about two months into our AX2012 R3 rollout, and we experience some severe slow downs on the Job Registration form in Manufacturing Execution. After some admitted non-SQL expert evaluation, I can find no resource issues, no blocks, no red flags on either the SQL 2014 database or the AOS. We have a twice daily Update Statistics job that runs (before and after our MRP runs) to keep the queries in order. As a desperate measure, during one of our shop floor slow downs, I ran the update statistics job. After the update, the performance dramatically improved. I have repeated this behavior several times now. So, given that updating the statistics is the cure, what can I do to stop the slow downs from occurring?
SQL2014, MOP 1, Autocreate statistics=True, AutoUpdate statistics=True, Trace Flags, 1224, 2371, 4199
Thanks!
*This post is locked for comments
Have you resolved this? A cu update may now help
Master planning takes can take lot of time to process hundreds of thousands of items, even you only have demand for a fraction of these items?. Many items without demand may be required in the system to allow look up of parts from older bills of materials, which are not sold actively anymore but are needed for warranty/or for maintenance or replacements and are only needed ‘on demand’.
You can set an item to get planned manually on different levels.
You can assign an item to a coverage group with coverage code Manual.
Master planning settings on a warehouse can be set to exclude items form the master plan.
Coverage settings for the item and for specific product dimensions set or a specific warehouse can be set to Manual.
Even when the items, or specific variants in a specific warehouse, are marked as Manual, MRP still has to check whether the item needed to be included in the plan. i.e. to check the settings that tells whether it is manually planned That is a price we pay for a very flexible way of setting up coverage settings.
So if MRP needs 10 milliseconds for each item to figure out if it needs to be included.
Using planning helpers can make a difference. But this is still quite a lot of work when you know many items
An update was released for Microsoft Dynamics AX 2012 R3 to help considerably.reduce Master planning run time - find the hotfix on LCS using issue search, entering hotfix KB 3159477:
https://fix.lcs.dynamics.com/Issue/Resolved?kb=3159477
The changes delivered in this hotfix are only triggered when running a Complete master plan..
This logic is is built to improve run time of complete master plans.
Should you run master planning for a subset of items by using the Select button in the master scheduling dialog form, then the new logic is NOT used, and the Master plan will process all the items which match the selection criteria entered. The Master plan should generally run for all items anyway, so ensure that, unless you want to update requirements for only a few items, that you clear the selection criteria.
Set the new Master planning parameters.
There are two new fields in the Master planning parameters form:
The Pre-processing: Automatically filter by items with direct demand parameter ensures that, during the pre-processing phase in the master plan, AX runs several queries which probe for demand in the following places:
If an item does not meet any of the above criteria, then it will not be included in the master plan.
Note: This also means that for an item, which has physically available on-hand inventory, which would have previously been included, and would show a requirement transaction for the on-hand inventory after the hotfix . then it will not show a requirement transaction , since there is no demand for the item.
With the list of items updated, MRP will t only run through these items during the next phases of the master plan after the pre-processing.
The Post-processing: Automatically filter by items with direct demand can also be set.
Just before the coverage step can start, there is a pre-coverage step, in which items with coverage setting “BOM version requirement” set to true need to be reprocessed, in order to make sure that the items from the required BOM are planned.
This may lead to situations where items, which in pre-processing were considered to have both on-hand and demand, could now have just on-hand and no demand, so they can be excluded from planning. If you do not use the “BOM version requirement” in your coverage groups, then you do not need to set this.
What difference does it make?
The actual impact on performance will depends on :.
Whether you have direct demand for almost every item, - then the difference will not be that big.
Whether the Master plan needs to do a lot of planned order scheduling, which is using up the majority of the time, - then the difference will not be that big.
The biggest differences you will see is when you have, many items e.g. 100,000, but only demand for 50.000 of these items. The overhead for checking the other 50.000 items before this update has a big impact on the update phase and coverage phase of the master plan. By filtering out all these items, it could save up to several hours.
The other related differences is the number of items written to your Master plan session logs. Whereas before, this would show all items that exist, it now only shows items with direct demand. The master plan is however treated as a complete plan, which makes sure that the ,plan version is renewed and that the InventSumLogTTS records get deleted.
The Number of planned orders should be the same as before. There can be a slight difference when items exist with physical negative stock levels.
When coverage settings for items show a minimum inventory level of 0, then planned orders are generated to correct this physical negative stock to 0. However, the queries that are looking for demand do not look at negative on-hand inventory when minimum inventory is 0. When you look at the design of this change, it makes sense, because a physical negative stock level does not necessarily mean there is demand.
Again, be aware that this change does not make a difference when you specify a filter when scheduling an MRP run. When you specify selection criteria, AX will simply schedule all the selected items, and not check to filter for demand.
Hi,
I suggest you check your AX database compatibility level.
SQL Server 2014 new Cardinality Estimator seems not to do well with AX database design.
So if you have new CE try downgrading your compatibility level to SQL Server 2012.
You may be surprised for the performance boost.
Sounds like the problem is in the statistics. At this point, you need to capture the query and analyze the query plan. Try to find why the statistics are causing a problem.
Analyzing a query plan can be a bit challenging if you are a beginner, I would recommend you to start with this nice video: https://www.youtube.com/watch?v=t7-b97jQVG8&index=1&list=PLGzzd7OgRNwa39r9oNB8_AWVhjrBLs7Td
You might also want to take a look at the trace flag 4139. Nothing says that you need this flag, but I have seen it in Dynamics AX/SQL configuration. Read about SQL Statistics Histogram : www.sqlservercentral.com/.../sql-server-part-2-all-about-sql-server-statistics-histogram
Our issue is during shop floor job registration. It was taking 30 seconds or more for the form to bring up the job list. We are currently doing a full statistics update 4 times per day. (it takes about 5-10 minutes) I will probably try to determine the tables that affect this an do a more frequent, but less complete update, but I haven't had the time.
You're updating statistics every 5 minutes throughout the Master planning execution window?
One last update on this one....I haven't really found a full solution. the DBCC FREEPROCCACHE did not make any difference. Updating Statistics makes a difference about 1/2 of the time. I did find a missing index on the DocuRef that made a difference. It seemed like a really odd table to be involved in this, but it did help. Overall, the performance is very, very inconsistent. I do have the Dynamics Performance analyzer running, but nothing is jumping out as a problem.
LJ
@lispyj From my understanding, I can't see how enabling DATAAREAID and PARTITIONID literal could help in your case since your have only one company and one partition.
@Brandon The article above explicitly says that all AOS servers must be restarted after applying DATAAREAIDLITERAL. it makes sense since the AOS reads SYSGLOBALPARAMETERS only when it starts.
I agree that trace flag 4136 is not recommended.
I wouldn't recommend 4136 anymore. Literals really solved the problem for the most part, and the value there is small with only one company. The nice thing is that you can try it in real time and turn it off if it doesn't work without restarting any servers or services, so there's no harm in trying it. Let us know how it works.
Statistics really does seem to be the solution in these cases. After the dramatic improvement it had on master planning in my environment (I'll be posting a follow-up to my original blog post soon), I've gone looking for other cases where it might be helpful. It's a little sad that SQL Server can't keep up with its statistics better than it does, but I understand your sentiment that it's a a baidaid where something more fundamental is probably wrong, but I disagree. Statistics are the meat and potatoes of query plan compilation. It's not just a bandaid to a deeper problem, it's the real problem in my opinion.
No! You might be onto something! As I read the information on trace flags, I did not enable 4136 because it didn't seem to apply. We only have one company and one partition. I will give it a try, though, because we certainly are showing the signs of parameter sniffing. Hopefully, that will make a difference.
Thanks for the input.
LJ
You didn't say so in your original post, but are you using Partition and DataAreaId literals since you didn't put 4136 in your list of trace flags?
#1
André Arnaud de Cal...
293,289
Super User 2025 Season 1
#2
Martin Dráb
232,068
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
Share
Report
All responses (
Answers (
