June 17, 2020 at 6:01 am
Hi
We enabled CDC for a few tables in a Dynamics AX database and immediately some AX batch jobs got stuck in deadlock. After disabling CDC, the batch jobs continued to run. So, does CDC take an exlusive lock for a table when you enable it?
Another hypothesis is that CDC slowed down the batch processing just enought to cause overlapping jobs which lead to a deadlock. Seems that deadlocks are not that uncommon in AX batch jobs. Still, I have hard time believing async CDC could have such an impact on performance, unless the system was already running on the brink of disaster. Anyway, I'm considering a switch to Change Tracking which is covered in AX documentation and would be adequate for our purposes.
I have very limited information about the problem details at the moment as I don't have access to the database and the dba is unavailable to answer questions.
June 17, 2020 at 8:08 am
normally with Dynamics applications you can't enable CDC at all - and you just saw one of the effects of doing so.
enabling CT or similar is your best option - I personally would use the option of ModifiedDateTime as documented here https://www.stephenmann.net/single-post/2015/04/17/Dynamics-AX-change-tracking
Regarding deadlocks - did your organization enable RCSI on AX? if not you would most likely wish to do so if you have many deadlocks
see https://docs.microsoft.com/en-us/dynamicsax-2012/appuser-itpro/configure-sql-server-and-storage-settings for info.
one note - it does mention to set MAXDOP to 1 - do not blindly follow this and test it instead as even with AX setting it to a higher value and also setting CTFP to a higher value can give better performance depending on the system usage. My advise here is to test it to see which value works well with your system.
June 17, 2020 at 8:45 am
normally with Dynamics applications you can't enable CDC at all - and you just saw one of the effects of doing so.
Can you tell more about these effects of CDC for AX, because I have yet to here any detailed technical reasons. There's increased log size and more write iops, but other than that CDC is async and disconnected from the original user facing transaction. I can see problems arising from CDC if the the db host is already close to maxing out available HW resources.
In Change Tracking I'm worried about querying data directly from the source tables. How is the execution plan stability with that, can it suddenly start doing full table scans if the amount of data to be fetched exceeds some optimizer index selectivity threshold for example.
Anyway, it seems I will switch to Change Tracking due to the official AX support. It's easy to setup and use, but so was CDC.
I'm more familiar with Oracle and there CDC has been rock solid in quite a busy system.
June 17, 2020 at 9:04 am
nI personally would use the option of ModifiedDateTime as documented here https://www.stephenmann.net/single-post/2015/04/17/Dynamics-AX-change-tracking
Regarding deadlocks - did your organization enable RCSI on AX?
Using a timestamp would require some artificial lag to be put into the incremental processing, because if the transaction completion is delayed the ModifiedDateTime of the row might not be visible when that particular time-bucket is processed. Now I'm reading data changes every few seconds to minimize lag. This is part of a bigger event sourcing architecture.
Snapshot Isolation would be good to have, but testing also that is out of my scope now. I have my hands full trying to coerce data out of these ERP's (yes, there's many and multiple flavours).
June 17, 2020 at 3:02 pm
frederico_fonseca wrote:normally with Dynamics applications you can't enable CDC at all - and you just saw one of the effects of doing so.
Can you tell more about these effects of CDC for AX, because I have yet to here any detailed technical reasons. There's increased log size and more write iops, but other than that CDC is async and disconnected from the original user facing transaction. I can see problems arising from CDC if the the db host is already close to maxing out available HW resources.
In Change Tracking I'm worried about querying data directly from the source tables. How is the execution plan stability with that, can it suddenly start doing full table scans if the amount of data to be fetched exceeds some optimizer index selectivity threshold for example.
Anyway, it seems I will switch to Change Tracking due to the official AX support. It's easy to setup and use, but so was CDC.
I'm more familiar with Oracle and there CDC has been rock solid in quite a busy system.
CDC on SQL Server also quite solid - but when enabling it on a table (and just at that point in time) it can lead to schema locks - quite fast normally.
Regarding Dynamics - no documentation as far as I am aware - but some of these details are often hidden deep on Microsoft site and can be rather hard to find.
Bottom line is that you may be able to enable CDC if you manage to figure out why the batches started to get locks - but be aware that it is possible that Microsoft will not support your AX if you do so (e.g. if it does not state on their documentation that it is supported for that particular application their stance is normally "we do not support you because you enabled something that we don't explicitly support")
CDC will also prevent AX from doing a table truncate - so any step that needs to do this as part of their normal processing will fail.
other issues with CDC is that any new column will not automatically be added to the underlying CDC setup
June 18, 2020 at 7:21 am
CDC will also prevent AX from doing a table truncate - so any step that needs to do this as part of their normal processing will fail.
other issues with CDC is that any new column will not automatically be added to the underlying CDC setup
Truncate is a difficult problem, although I would think it's a very limited set of tables that would experience that in an OLTP system. Column additions are probably manageable, but I think I will just rewrite our cdc-service to use Change Tracking.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply