Slow CPM journals XMLs posting in Dynamics - Workaround
Update received from microsoft on case ID: 2301110050002383
For Root cause, it comes down to a combination of two issues:
-
A known performance regression related to the functionality I had asked you turn off. The current workaround is to disable it using the feature we added for this specific situation. This is constantly under review for further fixes in terms of performance. So enable the feature to turn it off called "Turn off set based updates for customer settlement cash discount date". Lan / Dilan / Claire Cattell can do this.
You should be able to find it in feature management by just searching for “turn off” under the “All” features tab:
- A SQL Infrastructure issue that was causing a bad query plan to be used when there was a good query plan used earlier (back when performance was good). This comes down to an internal issue that is being reviewed due to this ticket. We will not be able to provide much information as the issue is internal.
Overall, there are some concerns regarding the throughput of your system (Importing and Posting Journals all while pushing them to BYOD), but the main cause of the problem (#2) was completely outside of your control. The engineering team is aware of the problem and it will be fully reviewed going forward.
The recommendation to disable the “set based” functionality (#1 above), is based on some recent findings in terms of Deadlocks, and it will be reviewed to determine how to avoid these for the specific datasets that were causing the problem. This has to do with how the data distribution in CustTransOpen varies during the day.
There isn’t anything that needs done on your end in terms of avoiding a recurrence. Simply keep an eye out for any odd performance degradation and reach out. We have documented the issue and future fixes should be pretty quick if they are needed at all. The fix we did should last a while, and is easily repeatable if need be. That said, hopefully before any recurrence, the SQL team on our end will be able to fix the infrastructure issue that caused the problem.