Improving the Nightly Data Sync Performance

Improving the Nightly Data Sync Performance

As discussed in the article Scheduling a Data Sync Multiple Times During the Day, It is possible to force the Data Sync to run more than once during the day. The downside of doing so is that the data sync is fairly resource-intensive, and this may impact the performance of the site while the sync is running. When the Data Sync runs, it is effectively pulling all users and computers from the Config Manager database, and is either inserting (new) or updating (existing) records in the App Portal database. To improve performance, it is possible to limit the records being synced to those that have actually been created in the last day (or even the last hour). To do this, all that would be required would be to use a Custom User ID and Custom Computer ID sync query, that checked the creation date of the record in Config Manager. The following queries will limit the sync to those newly created records:

Custom User ID sync query

IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
    SELECT vru.ResourceID as UserResourceID FROM v_R_User vru ORDER BY vru.Name0 
ELSE
    SELECT vru.ResourceID as UserResourceID from v_R_User vru where vru.Creation_Date0  >= DATEADD(day,-1,GETDATE()) ORDER BY vru.Name0;

Custom Computer ID sync query

IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
      SELECT vrs.ResourceID FROM v_R_System vrs WHERE vrs.Netbios_Name0 is not null ORDER BY vrs.Netbios_Name0;
ELSE
       SELECT vrs.ResourceID from v_r_system vrs where vrs.Creation_Date0  >= DATEADD(day,-1,GETDATE()) AND vrs.Netbios_Name0 is not null ORDER BY vrs.Netbios_Name0;

You will note that the first part of the query, namely:

IF DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')

indicates that the full query should run on Saturday and Sunday, while the "Delta" sync should be run Monday-Friday.  This allows for old records to be updated over the weekend, just in case. 

The Entering Common Deployment Technology Settings topic in the Administration Guide provides additional details on entering custom sync query settings.

NOTE: Once the sync settings are changed, be sure to restart the ESD Service so that the new settings will be picked up. 

In addition to modifying the User and Computer Sync ID queries,  increasing the Sync Page Size can significantly decrease the time that the sync takes to run. I would recommend setting the Sync Page Size to a value between 5000 and 10000. The Common Tab topic in the Administration Guide documents this setting. 

Labels (2)
Was this article helpful? Yes No
No ratings
Comments

Charles,

Does this affect the datasync when a user logs in during the day and the sync updates or imports that record for the specific user?  Or has that sync been removed?  

Version history
Last update:
‎Dec 28, 2021 01:02 PM
Updated by:
Contributors