Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Sync Taking 30 Minutes For Small Amount Of Data - Seems Dependent On If User Attempts Sync While Changes Are Being Made RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    We are using the Microsoft Sync Framework to synchronize tables and data between a SQL Server 2008 R2 DB and local SQL CE DBs on WPF based desktop clients to enable local processing scenarios, however we have an issue that occurs when a user opens the client and performs a sync of a table while or just after a large number of changes have been made to a source table (the table the client is syncing from).

    We are downloading changes only from the server to the client and not sending changes from the client to the server when this issue occurs.

    Example: User opens the appplication and tables X, Y, and Z sync only the changes.  If the user happens to open the application while a serverside process is making or just made a series of changes to those tables it can take up to 30 minutes for the sync to complete.  If the user waits until after the server side changes to the source tables are known to be complete it takes less than 5 minutes to sync.

    Is there any way to avoid or work around this issue?  Ideally it would not exist, but if it must exist could you explain the reasons and how we could work around it (e.g. how to get the last set of changes to the table's data, add a field that you check to determine if serverside processing is ongoing and present the user with a message, etc.).  Our client code for the desktop application is in C#.

    It seems strange.

    Wednesday, April 27, 2011 4:31 PM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    most likely, your sync process is waiting for locks to be released by the server process. you can verify if the sync connection is waiting or is being blocked by viewing process information in SQL Server using Activity Monitor.

    you might want to check the type of locks and what objects are being locked by the server process when it does its updates and how long before it commits its changes.

    Wednesday, April 27, 2011 4:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Ok thanks JuneT.  If we discover the locks, then what courses of action do we have?
    Wednesday, April 27, 2011 6:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    With one SQL Server, we have tested that multiple concurrent sync can run well together. When you see such kind of significant slow down during concurrent operations and sync, you may want to check if there are long running inefficient queries/transactions or dead locks. It is a SQL Server topic, and you can find many links online for how to minimize SQL Server blocking. I copied a couple links below:

    http://technet.microsoft.com/en-us/magazine/2008.04.blocking.aspx?pr=blog

    http://www.sql-server-performance.com/tips/blocking_p1.aspx

    Thanks,
    Dong

     

     

    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, April 28, 2011 6:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    as Dong mentioned, you may want to address the slow running/blocking queries.

    if you cant address it now, you can at least estimate how long the server process runs and configure when to re-initiate the sync.

    as you mentioned, you can configure a particular table to contain a flag to specify that the server process is running and you can check that before synching. you can then retry the sync after a particular period of time based on how long the server process runs.

    Friday, April 29, 2011 1:18 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JuneT and Dong,
    In an interesting twist to this issue the locks do not seem to last long, but the syncs are taking a long time for users with lower amounts of memory and CPU power.  For example our 30 minute sync times occur on a user that only has 1 GB of memory and a single core AMD Turion running Windows XP.  The user's CPU and memory utilization go up at or above 90% several times during the sync process.  While at the exact same time someone with 4 GB of memory, a dual or quad core CPU, and Windows 7 can do a sync at the same time in < 3 minutes for the same set of data.

    So it is seeming CPU or memory bound.  Is there something that can be done for users that have smaller amounts of RAM?

    Friday, April 29, 2011 3:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    out of curiousity, how big is the data being synched? which provider are you using? can you enable Sync Fx tracing to log more information on what's happening?
    Friday, April 29, 2011 10:24 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi JuneT,

    It is 400,000-450,000 rows of data across several tables (one table has ~323,000 of the records though).  We allow "what-if" scenarios and analysis for the users which for our app is best done locally on their computer.  We are using the DBServerSyncProvider on the host and the SqlCeClientSyncProvider for the local embedded DB.

    Friday, April 29, 2011 3:17 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    With 400,000+ rows in one sync, it can consume a lot of memory if you didn't set MamoryDataCacheSize (http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.relationalsyncprovider.memorydatacachesize.aspx). Please try to set it to a proper value that matches to your machine hardware.

    Thanks,
    Dong

    This posting is provided AS IS with no warranties, and confers no rights.
    Friday, April 29, 2011 4:54 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    unfortunately, MamoryDataCacheSize  is for the SqlSyncProvider and SqlCeSyncProvider and is not available for your DBServerSyncProvider/SqlCeClientSyncProvider combo.

    so you can either change providers or try to use DataSetSurrogates and see if it improves performance (see this link: http://jtabadero.wordpress.com/2010/03/08/sync-framework-wcf-based-synchronization-for-offline-scenario-%e2%80%93-using-custom-dataset-serialization/)

    Saturday, April 30, 2011 1:52 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks June.  We have enabled sync tracing to get to the bottom of this and have the ability to change the base provider for the host side of the sync from DBServerSyncProvider to SqlSyncProvider.  If you have time, I had one more query.  The user (singular) that is experiencing the long sync times is the user that has made the most local changes.  Is it possible that the sync is taking longer for that user as more changes need to be compared?  This is a general question as we'll still go down an investigative route as this the same user that also has 1 GB of memory.
    Wednesday, May 4, 2011 3:10 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    actually, Sync Fx doesnt do change comparison(e.g., comparing row by row). it simply retrieves what has changed and applies it. if it is unable to apply the change, it would raise it as a conflict (e.g., it tries to apply an update to an already deleted row).

    have you checked for conficts in the server provider when it applies the client changes (via ApplyChangesFailed)?

    the other thing with the SqlCeClientSyncProvider/DbServerSyncProvider is the SyncContext contains multiple copies of the same dataset containing the changes being applied (e.g., check the SyncContext return value of ApplyChanges and you'll see it contains the dataset of what has been applied, a dataset for conflicts, etc...)

    the Sync Fx trace logs should be able to provide us more insight on whats going on under the hood.

    you can also check this to optimize synching: http://blogs.msdn.com/b/sync/archive/2011/03/02/synchronization-services-for-ado-net-for-devices-improving-performance-by-skipping-tables-that-don-t-need-synchronization.aspx

     

    Wednesday, May 4, 2011 11:37 PM