Philippsen's Blog

Everyday findings in my world of .net and related stuff

Archive for January, 2016

Deadlocks show up in AIF exception log when CRM synchronizes multiple companies.

Posted by Torben M. Philippsen on January 25, 2016

Recently I was working on a case where some issues occured when trying to synhronize data between MS dynamics ax 2012 and MS CRM.

Scenario description:

In MS CRM the following maps have been set up for synchronization in two integrations.

CRM_maps

Each integration uses its own AX user for connection.

From the CRM point of view, the synchronization seems to succeed. However going through the exception log in AX it seems that quite a few deadlocks have occurred, please see screendump below:

AX_deadlocks

And here’s a screendump from the “general” tab showing the error message – “Cannot select a record in Change Tracking Version (AifSqlCtVersion). Change Tracking Version: 0.
Deadlock, where one or more users have simultaneously locked the whole table or part of it.
”:

AX_deadlock_general

The deadlocks seems to have disappeared by themselves.
From this blog post this issue seems to be a known issue – https://community.dynamics.com/ax/f/33/t/160640 – however the ax database had not recently been updated.

 

The cause:

The cause seems to be the SQL optimiser doing a clustered index scan (RecId) in the query below, resulting in blocking:

SELECT T1.CTVERSION,T1.CREATEDDATETIME,T1.RECVERSION,T1.RECID FROM AIFSQLCTVERSION T1 WITH ( UPDLOCK) WHERE (CTVERSION<@P1)

 

The solution:

  • Login to Dynamics AX –> Open a Development workspace, and navigate to AOT –> Dictionary -> tables,
    Find the table AIFSQLCTVERSION , and create a new non-unique index for CTVERSION field
  • Login to SQL server Management studio, and create a new plan guide for the query adding index hint
    EXEC sp_create_plan_guide @name = N'[PlanGuide-AIFSQLCTVERSION]’, @stmt = N’SELECT T1.CTVERSION,T1.CREATEDDATETIME,T1.RECVERSION,T1.RECID FROM AIFSQLCTVERSION T1 WITH ( UPDLOCK) WHERE (CTVERSION<@P1)’, @type = N’SQL’, @module_or_batch = N’SELECT T1.CTVERSION,T1.CREATEDDATETIME,T1.RECVERSION,T1.RECID FROM AIFSQLCTVERSION T1 WITH ( UPDLOCK) WHERE (CTVERSION<@P1)’, @params = N’@P1 bigint’, @hints = N’OPTION (TABLE HINT (T1, INDEX( I_100021KOO_CTVERSIONIDX), UPDLOCK))’
    GO(replace the index name I_100021KOO_CTVERSIONIDX with the actual name of the newly created index)

 

Advertisements

Posted in AIF, Microsoft Dynamics AX | Tagged: , | Comments Off on Deadlocks show up in AIF exception log when CRM synchronizes multiple companies.

Microsoft CRM: Synchronizing with multiple AX companies results in continuously opening new active AOS-connections

Posted by Torben M. Philippsen on January 25, 2016

Recently I was presented with a very interesting problem that occured when Microsoft CRM was synchronizing with Microsoft Dynamics AX 2012 using the DynamicsConnector for MS Dynamics AX. The installation guide can be found here.

In basics it was a standard setup in both ends. When syncing only one company everything would work just fine, but when syncing two or more companies the online users would be filled with active connections that wouldn’t close by themselves. In the end this would lead to the AOS not accepting new clients or even a crash due to lack of RAM.

The strange thing was that everything was setup according to the guidelines and was working fine in a reference environment. A MS support request was created and I will try to summarize all of our findings and the solution that fixed the problem.

The problem in short:

  • When running the synch of 2 or more companies simultaneously, the client session on AX continues to increase till the crash of AOS.
  • Running synch of 1 company only (in other words 1 integration) the client sessions do not increase; looks like they are recycled.
  • The problem occurs in all maps and seems to be a general problem in our scenario.
  • The synchronization for all companies is running on the same AX account.

The solution:

  • The verified solution from MS was to create separate accounts for each synchronization.

Posted in AIF, Microsoft Dynamics AX | Tagged: , , | Comments Off on Microsoft CRM: Synchronizing with multiple AX companies results in continuously opening new active AOS-connections

 
%d bloggers like this: