APP: High SQL Server CPU usage on user synchronization

Sympton

A user tries to synchronize data with Microsoft Dynamics NAV, but no data-updates are sent out. The SQL server gehts a very high CPU usage (80-100%) on at least one Core.

This also affects other Anveo users and normal Mircosoft Dynamics NAV users.

Cause

Anveo Delta Server calculates changes for each user. If a lot of changes are open, in some cases a SQL index is missing to calculate changes in just a few seconds.

Solution

1. Open SQL Server Management Studio
   
2. Right-click your SQL Server in the list - select Activity Monitor
   
   
   
   
3. Open tab Recent Expensive Queries and right-click the one with highest CPU (ms/sec) usage. Select Show Execution Plan
   
   
   
   
4. Analyse the execution plan: SQL server gives you information about where in the SQL query the time is consumed (in %) and also provides an missing index hint.
   Right click your execution plan and open this execution plan as an xml document.
   
   
5. Add the missing indexes into your Microsoft Dynamics NAV table.
   Required fields are shown in XML document in section MissingIndex - ColumnGroup
   
   
   
   
6. Restart
   - Cancel sync on your device
   - Restart Microsoft Dynamics NAV server (web services)
   - In Anveo Setup: Reset Processing Flag (All Users) because you have interrupted a (long) running synchronization by restarting Microsoft Dynamics NAV server. (Anveo version >= 4.14)
   
   
   
   
7. Restart your synchronization on your mobile device

It may be required to walk through these steps multiple times due to different tables and fields.

Note

Based on your changed data, synchronization may be fast for serveral weeks. First time, a user does a big change on specific data, these steps may be required. Therefore, this is also required even some weeks after a go-live.