none
metadataCleanup.PerformCleanup() RRS feed

  • Question

  • I am using sync framework 2.1,sqlsyncprovider

    In my case I have two scopes

    scope 1: 15 tables (3 -4 tables which are largest tables in that database): sync runs every 30 secs

    scope 2: 127 tables ( table size range from small to medium): sync runs every 2 minutes

    Initially, sync for both scopes are fast. But after subsequent sync cycles (after 2- 3 days) suddenly I see performance issue in scope 1 sync.

    When I enabled the trace, I see the getknowledge method call on remote provider is taking 10 minutes to process and move to next method call Applychanges. So because of it the sync cycle completion time has been increased ( from 10 sec to 12 min when there is no change in the two nodes).

    Is this expected behavior in sync?

    Process action 'http://tempuri.org/IRelationalSyncContract/GetKnowledge'
    Process action 'http://tempuri.org/IRelationalSyncContract/ApplyChanges'

    In my case delete operations are less. 98% of the time, we have inserts, updates only.

    I am going through metadata cleanup process in the following link:

    http://msdn.microsoft.com/en-us/library/ee617373.aspx

    What I understand from the link is it deletes metadata of rows that are deleted from primary tables. As I said earlier I have only few deletes. How this helps my case?

    There are no conflicts in my sync. Even a conflict arise , I am resolving it in the code!! 

    Please advice what to do to clear the knowledge in scope_info table!!



    ess


    • Edited by saitrips Wednesday, April 17, 2013 12:40 PM
    Wednesday, April 17, 2013 2:25 AM

All replies

  • your sync intervals are too short. have you looked at using SQL Profiler if there's contention on the tables? or have you checked your sync knowledge size?

    Tuesday, April 23, 2013 10:52 AM
    Moderator
  • What does it mean by sync intervals are too short ?  do we need to have large time gap between each sync interval. 

    sync knowledge size (in scope_info) is 30MB.

    thk you for your help.


    ess

    Tuesday, April 23, 2013 11:11 AM
  • that's 30mb going across the line and that's serialized/deserialized if am not mistaken.

    how many clients do you have synching? is this hub-spoke where you have multiple clients with their respective scopes synching against a single server scope?

    Tuesday, April 23, 2013 1:02 PM
    Moderator
  • Hi JuneT,

    how many clients do you have synching?  Ans: One

    may be I exaggerated the size of scope_ sync_knowledge.

    But one thing for sure, size of  scope_ sync_knowledge column in scope_info table increased after subsequent syncs and sync started taking long time time to compare the two replicas knowledge.

    Any clue. Is this expected behavior in sync when metadata grows?



    ess



    • Edited by saitrips Wednesday, April 24, 2013 12:02 AM
    Tuesday, April 23, 2013 1:46 PM
  • Your knoledge also records the failed rows.  There are 2 ways

    1.  Completely emty out the knowledge and start over the sync

    2.  Trace DbSyncFailedEventArgs which will have failed row(s) and a rason for failure then fix all the failed rows until you bring down to 0 row failed at this time you knowledge will be back to original size.  Every time you fix failed rows you may need to perform sync atleast twice to resolve the conflict correctly

    Hope this helps.

    Thanks & Regards,

    Udai.

    Tuesday, April 23, 2013 3:30 PM
  • hI udai,

    1.  Completely emty out the knowledge and start over the sync

    you mean:

    update scope_info

    set scope_sync_knowledge=null

    2.  Trace DbSyncFailedEventArgs which will have failed row(s) and a rason for failure then fix all the failed rows until you bring down to 0 row failed at this time you knowledge will be back to original size.  Every time you fix failed rows you may need to perform sync atleast twice to resolve the conflict correctly

    I checked I don't have conflict rows.

    Thnk a ton in taking time to help me!!


    ess

    Wednesday, April 24, 2013 12:05 AM
  • Yes this is how you will empty the knowledge.  But the downside of this approach is it will restart the sync from the beginining instead you can start over the initial sync which will have clean tracking information.  Also you can check your SQL database dbcc check to make sure your sending/receiving database did not have any constraint issue.

    update scope_info

    set scope_sync_knowledge=null

    Check the provider you might be having something like this

     provider.ApplyChangeFailed += new EventHandler<...>

    above event will return rows related to constraint issue entries.

    Wednesday, April 24, 2013 12:23 AM
  • setting the knowledge to null is almost the equivalent of deprovisioning and reprovisioning minus the creation of the sync  objects. and no, cleaning up that sync knowledge doesnt clean up the change tracking. that's separate.

    if you have data on both replicas, that's going to cause conflicts and on a big database, a slow first sync.

    sailtrips - how are you resolving conflicts? are you using Retry On Next Sync? that's the only reason for conflicts to bloat the sync knowledge.


    Wednesday, April 24, 2013 1:02 AM
    Moderator
  • JuneT,

    public void localProvider_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)

    {

    if (e.Conflict.Type == DbConflictType.LocalUpdateRemoteUpdate)
                        {
                            e.Action = ApplyAction.Continue;
                        }
    }

    like this, for all conflicts I am using  ApplyAction.Continue.

    ess

    Wednesday, April 24, 2013 1:49 AM
  • JuneT,

    Is sync knowledge stores information of all modified rows in the peers. If that is the case, at some point (x days) it's size increase and performance issue arise.

    Is there any setting in sync framework to avoid this.


    ess

    Wednesday, April 24, 2013 2:10 AM
  • Hi JuneT, Udai,

    JuneT: I deprov and re prov the dataabse to get you right metrics on sync knowledge. I checked the size of scope_sync_knowledge in scope_info table, it is increased from 4kb to  25 KB  in 24 hours

    For time being we will park the performance issue aside. Could you please tell me first: why sync knowledge (scope_sync_knowledge) grows ?

    Because of:

    1)unresolved conflicts.

    2)or it stores info of all modified and new rows,deletes row info in the knowledge.

    I am positive  that I am resolving conflicts in my code and we have very fewer deletes compared to modification to existing rows ,adding new rows and I am  not using any filters.

    JuneT: In the following thread you mentioned that " total number of rows being synced will entries in the sync knowledge,  likewise, the number of entries for each row inside the sync knowledge is not equal to the number of changes you make to a row (if you update a row 100 times, sync framework doesn't store 100 entries in the sync knowledge for the 100 updates made). "

    I understood that, sync knowledge stores info regarding records to be synced. After the sync is complete, it will remove successfully synced records information from knowledge and just leaving conflict records still in knowledge. I will repeat the process every time u call for sync.

    Please correct me.

    http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/5e6eadee-d8c3-40aa-b065-21f9d77eafa3

    Please advice


    ess





    • Edited by saitrips Thursday, April 25, 2013 1:45 PM
    Thursday, April 25, 2013 12:25 PM
  • #2 - no, it doesn't store those info, otherwise that would have been bigger.

    afaik, Apply.Continue is not storing conflict information in the sync knowledge. 

    can you try subscribing to SyncProgress event, then inspect the ScopeMetadata property of the event argument to look the sync knowledge.

    Thursday, April 25, 2013 1:38 PM
    Moderator
  • I tried what you mentioned(check scopemetadata property):

            localProvider.SyncProgress += new EventHandler<DbSyncProgressEventArgs>(localProvider_progress);
           remoteProvider.SyncProgress += new EventHandler<DbSyncProgressEventArgs>(remoteProvider_progress);            

          public void localProvider_progress(object sender, DbSyncProgressEventArgs e)
                {
                }
           public void remoteProvider_progress(object sender, DbSyncProgressEventArgs e)
                {
                }

    When I looked at scopemetadata property in eventargs e, It's not human readable data. Its is kind of big varbinary number. No clue wht to do

    JuneT: In the following thread you mentioned that " total number of rows being synced will entries in the sync knowledge,  likewise, the number of entries for each row inside the sync knowledge is not equal to the number of changes you make to a row (if you update a row 100 times, sync framework doesn't store 100 entries in the sync knowledge for the 100 updates made). "

    what I learned from above paragraph is, sync knowledge stores info regarding records to be synced. After the sync is complete, it will remove successfully synced records information from knowledge and just leaving conflict records still in knowledge. I will repeat the process every time u call for sync.

    Please correct me.

    http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/5e6eadee-d8c3-40aa-b065-21f9d77eafa3

                                 

    ess


    • Edited by saitrips Friday, April 26, 2013 3:15 PM
    Thursday, April 25, 2013 2:17 PM