Hi All,
One of the Dynamics CRM 2015 apps has some data import workflows that worked previously, before I even got put in charge of this app. Months ago, the business admin, who imports the csv files into the wizard, noticed the data not getting updated properly. I've scratched my head at those for months, so im really hoping to get some help here!
The csv files can have anywhere from 5k records to 40k. At one point, the 40k record file was being processed at just fine, but now even a few hundred records get hung up.
Here the process:
- The files get imported
- Each record gets created as a Account staging record into CRM
- On creation of the staging records, Workflows get kicked off to determine what to do with this data.
The first SearchAccount plugin activity queries the Account entity with the staging record entity data, outputs the matched account reference, if any, and an action whether to Create, Update, or Deactivate.
Based on that action from SearchAccount, it then goes into CreateAccount, UpdateAccount, or Deactivate plugin activity.
It then does some similar logic on the Contacts for the Accounts.
There are some additional workflows that happen after all this, but the workflows for the Accounts are severely impacted and process very slowly so the others dont get a chance to run. Out of the file with 5k records, maybe 100 go through successfully after an hour then eventually stick in Waiting with a timeout, "Generic SQL error", or suspended. I got to a point in my UAT environment where the success rate was almost perfect, which I would settle with for now since I want to move this whole solution to Datastage/SSIS and the admin as the abiliy to select the failed ones to re run, but this is bugging me like crazy.
What got me to my success rate in UAT was adding the Sandbox Timeouts registry edits on the BE servers and increasing them like here: http://balarajann.blogspot.com/2015/05/how-to-increase-sandbox-time-out-above.html I also had the DBA change the RCSI propery like here:https://willwarren.com/2015/10/12/sql-server-read-committed-snapshot/
I made my changes to Prod and nothing happened. When I run the imports and watch the logs on the SQL DB Server, I see SQL deadlocks on the dbo.ProcessSessionBase table. The deadlock reports also have details like the process {hostname}.MSCRMAsyncservice and the user running it SI_FltCRMAsync. Im really leaning towards an issue with this applications database, but my DBA wasnt able to identify anything. I know if I went into depth on the plugin code and logic, I would probably get told to refactor a lot. Due to restraints on my knowledge of Dynamics, other active projects I need to focus on, etc that's something I cant take on right now. It doesnt make sense to me that Prod is the only instance where these issues occur, even if the imports are ran off business hours where users are not on the system.
The Assembly with the plugin activities in Sandbox mode.
This is not something happening in UAT, so Im at a complete loss! I cant find anything on the internet about this particular scenario.
Ive follow the steps to clean out asyncoperationbase table: https://support.microsoft.com/en-us/help/968520/performance-is-slow-if-the-asyncoperationbase-table-becomes-too-large
Ive restarted BE servers, CRM services, and iisresets on the FE servers. There are 2 other apps on the platform and they dont have these issues.
I appreciate any recommendations or help in advance!!
