Database Sync - Performance RRS feed

  • Question

  • Hi,

    We are using DB Sync over WCF to sync our CE DB.  We are trying to figure out why syncing has now started to take so long it always times out.  We have went from less than 5 minutes to taking more than 30.

    Our source DB is very small.  There is one table that does have 12K rows but that table only has two columns that are ints.

    What appears to be happening is when we monitor the destination there is data coming down as we look at network traffic but the CE DB only grows to about 1/3 it's expected size and then the system just "churns".  I'm wondering if changes are trying to be applied to the CE DB but for some reason it is taking so long it causes it to not complete.

    We feel like there haven't been any significant changes in our code base that would be causing this so I'm just wondering if there is something "behind the scenes" in Sync that I'm overlooking such as "resetting" sync values in the source db (http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.sqlserver.sqlsyncstorerestore.performpostrestorefixup.aspx)?

    Thanks in advance!

    Wednesday, August 29, 2012 2:38 PM

All replies

  • there is no performpostrestorefixup for SQL CE and you only use it when restoring databases.

    try enabling sync framework tracing to see what's going on and where it's stuck.

    Thursday, August 30, 2012 11:09 AM
  • We did restore our source database (MS SQL) and that is why I was wondering if performpostrestorefixup would need to be ran against our central db?
    Thursday, August 30, 2012 11:29 AM
  • If you have restored the database, then make sure you run performpostrestorefixup before synching...
    Thursday, August 30, 2012 1:23 PM
  • Thanks for you help so far, but after trying that it did not help.

    I did turn on sync tracing on the server hosting our WCF services.  When I look at the trace file, I see a big gag in time between the following lines (See BOLD):

    VERBOSE, w3wp, 22, 08/30/2012 15:17:44:159, Reading Schema Version Info
    VERBOSE, w3wp, 22, 08/30/2012 15:17:44:159,    Executing Command: SELECT [schema_major_version], [schema_minor_version], [schema_extended_info] FROM [schema_info]
    VERBOSE, w3wp, 22, 08/30/2012 15:17:44:185,    Executing Command: SELECT [scope_id], [scope_local_id], [scope_sync_knowledge], [scope_tombstone_cleanup_knowledge], [scope_timestamp], [scope_config_id], [scope_restore_count] FROM [scope_info] WHERE [sync_scope_name] = @sync_scope_name
    VERBOSE, w3wp, 22, 08/30/2012 15:17:44:185,       Parameter: @sync_scope_name Len: 13 Value: GTB_SOL_scope
    VERBOSE, w3wp, 22, 08/30/2012 15:17:44:270, Closing Connection
    VERBOSE, w3wp, 12, 08/30/2012 15:28:55:425, Connecting using string: Data Source=BLAH Catalog=BLAH;User ID=BLAH;MultipleActiveResultSets=True
    VERBOSE, w3wp, 12, 08/30/2012 15:28:55:460,    Executing Command: SELECT @sync_new_timestamp = min_active_rowversion() - 1

    When I run the following query:

    SELECT [scope_id], [scope_local_id], [scope_sync_knowledge], [scope_tombstone_cleanup_knowledge], [scope_timestamp], [scope_config_id], [scope_restore_count] FROM [scope_info] 

    even on the server that takes several seconds.  Is it actually bringing down the contents of  [scope_sync_knowledge] and applying to the CE DB?  That column appears to have a significant amount of data (when I run DATALENGTH it returns 1055344).

    Thursday, August 30, 2012 4:07 PM
  • do you do a lot of deletes?

    try running a PerformCleanup, you can find this in the documentation.

    no, sync fx doesnt apply the sync knowledge from the server to the client or vice versa, but it uses it to determine what changes need to be sent.

    Friday, August 31, 2012 1:34 AM
  • I tried PerformCleanup on my "master" database and still not having any luck.  Our "master" DB appears to be about 350MB total size.  

    This is the scenario we are currently struggling through:

    1) We have a SQL CE db with just the tables, none of the syncing tables.  We run sync on this DB over WCF and eventually the CE DB is created with the syncing tables.  Takes about 20 minutes to complete.  I see the SQL CE grow to about 4180K during the sync process.

    2) I then run the sync again.  There have been zero changes on the master DB nor the CE.  I would think this sync would be quick as there is very little work to do.  But after about 20 minutes we time out with the following exception: 

    The request channel timed out while waiting for a reply after 00:14:28.6875929. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout.

    This error is thrown in this  method:

            public override void ProcessChangeBatch(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges, object changeDataRetriever, SyncCallbacks syncCallbacks, SyncSessionStatistics sessionStatistics)
                DbSyncContext context = changeDataRetriever as DbSyncContext;
                if (context != null && context.IsDataBatched)
                    string fileName = new FileInfo(context.BatchFileName).Name;
                    //Retrieve the remote peer id from the MadeWithKnowledge.ReplicaId. MadeWithKnowledge is the local knowledge of the peer 
                    //that is enumerating the changes.
                    string peerId = context.MadeWithKnowledge.ReplicaId.ToString();
                    //Check to see if service already has this file
                    if (!this.proxy.HasUploadedBatchFile(fileName, peerId))
                        //Upload this file to remote service
                        FileStream stream = new FileStream(context.BatchFileName, FileMode.Open, FileAccess.Read);
                        byte[] contents = new byte[stream.Length];
                        using (stream)
                            stream.Read(contents, 0, contents.Length);
                        this.proxy.UploadBatchFile(fileName, contents, peerId);
                    context.BatchFileName = fileName;
                SyncSessionStatistics stats = this.proxy.ApplyChanges(resolutionPolicy, sourceChanges, changeDataRetriever);
                sessionStatistics.ChangesApplied += stats.ChangesApplied;
                sessionStatistics.ChangesFailed += stats.ChangesFailed;

    On the line:

                SyncSessionStatistics stats = this.proxy.ApplyChanges(resolutionPolicy, sourceChanges, changeDataRetriever);

    Any other ideas to help?

    Friday, August 31, 2012 5:07 PM
  • what was the retentionperiod you used for PerformCleanup? it there wasnt much deletes, then it would have much impact.

    again, i would suggest you post a trace up to the point where it actually fails.

    Monday, September 3, 2012 1:25 AM
  • Probably you can try to use the PSSDiag tool to do some data capture during the synchronization process. The data capture can help you determine which SP/SQL query is causing/contributing to the problem.
    Thursday, October 4, 2012 9:04 PM