locked
Increasing Microsoft Sync Framework Performance RRS feed

  • Question

  • Dear,

    I have an SQL Server 2005 database and about 500 SQL CE databases in each client application. Syncing process goes slowly. In several times the application is not responding. I believe that the data traffic consumes massive bandwidth during the process. I'm wondering whether there is any mechanism to speed up the performance. Please advice.

    Thank you for any help.

    Agung
    Friday, September 18, 2009 6:00 AM

All replies

  • Are you using Sync Services for ADO.NET?
    Monday, September 21, 2009 7:20 PM
    Moderator
  • Are you using Sync Services for ADO.NET?

    Yes I am.
    Could you please advice about the performance issue?
    Wednesday, September 23, 2009 1:25 AM
  • Interesting. before we jump into suggestions, a few questions about the scenario:
    are those 500 clients sync-es to the server simutanously ?
    are they on Windows Mobile devices or on desktop ?
    how much data being sync-ed ?
    is this a upload sync or a download sync ?
    what is the network bandwidth ?
    if possible, would the entire sync time to be break down to time spend on server, time spend on client, by hooking up to the GetChanges, ApplyChanges event on the server provider and the client provider ?

    thanks
    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, October 6, 2009 1:24 AM
    Moderator
  • Hi

    I hope you don't mind if I jump in on this but I have a working knowledge of this system also, so will try and answer those questions.

    There are actually 900 smart client applications out in the field and they reside on desktop/laptops and are not mobile devices The maximum concurrent use we have seen in two months is 160 users but this has brought the system to a virtual standstill.

    The system was using sync framework V1 but has been upgraded to V2 CTP 1.

    I am not sure why but all users (900) have a seperate sql login to the db rather than accessing through another layer. When installed the initial sync, which can include historical information can be quite large however, ongoing, the information transfered should be relatively small albeit some form large tables >500000 <1000000 rows.

    In total 97 tables (896 columns) are syncronised (two way), and these are mostly scheduled at different times i.e. 15 minute, 30minute, 1 hour etc.

    We are seeing high wait times on the db and this is leading to timeouts and in extreme cases bring the whole system to a halt. SQL wait times are definately the problem on the server rather than bandwidth or i/o but on the clients I believe bandwidth causing issues.

    It also looks as though the original developers got a little carried away with sync services and a lot of information is transfered using sync services when it is unnecassary.

    My initial impressions are that the implementation of sync services is as much to blame as the framework itself for the performance problems we are seeing and whilst we were looking at options to remove the framework, my current thinking, is to adjust the implementation to reduce the amount of syncronised tables, serialise the data transfer and expose the Sync Framework 2.0 via WCF to remove the multiple SQL users. We are also investigating an upgrade to sql server 2008 to take advantage of integrated change tracking.

    Hopefully that helps explains the situation in more detail, I would love to hear views on the current implementation and more impotantly any advice to help us move forward would be greatly appreciated.

    John.

     
    Tuesday, October 6, 2009 5:46 AM
  • Thanks for the info about the system.

    so apprently you have already done some analysis to identify the first bottleneck that affect perf/scale here which is the SQL Server.

    besides what you ahve desribed to improve, a couple more suggestions that would also reduce the load on the backend Sqlserver:
    1. seperate the tables to different table groupes, ---since the sync services uses signle transaction for each table groups, reduce the size could reduce the transaction size.
    2. use downlaod only if possbile.
    3. only sync tables that have changes -- this will require some logic in app layer to determine the tabel list
    4. sync more frequently to get smaller data at a time


    This is an interesting system. Please keep us posted and share the information on how to improve the perf/scalability with sync services. We would like to hear the update on this.


    thanks
    Yunwen

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, October 8, 2009 11:26 PM
    Moderator