SQL Server 2005 vs 2008 Sync Performance RRS feed

  • Question

  • We have a compact framework application that runs on WinCE 5.0 and uses a SQL Server CE database. Sync Services 2.1 is used to synchronize the .sdf to a SQL Server 2005 database on the server side.  There are a few tables that have up to 200K records that consist of just a few columns.  Right now the sync process is taking too long (~3-5 minutes) and we are looking for ways to improve performance.

    I understand that SQL Server 2008 introduced "change tracking" which sounds like it will improve performance on the server side by eliminating the need for triggers.  We've ran some SQL traces and it appears as if the bottleneck is on the device as opposed to the server.  I am wondering if change tracking will improve overall efficiency or just increase server side performance.  

    We could ask the client to upgrade the database server to SQL Server 2008 but wasn't sure how big an impact it would have if the device is where the slowdown is.  Any thoughts on that?

    Monday, June 13, 2011 2:04 AM

All replies

  • you might want to enable Sync Fx tracing to identify where the bottlenecks are. Theres hasnt been any update on the SQLCEClientSyncProvider but there are some steps you can do to improve performance. for a start, you can take a look at this: 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
    Monday, June 13, 2011 2:24 AM
  • Thanks for the quick response.  We are not syncing any tables that don't need to be synced.  So, unfortunately, that won't help in this scenario.  I'm not familiar w/SyncFX tracing so I did a quick search and found the following article.


    I'm not sure if we've enabled tracing within sync services yet or not but will bounce that off the team.  Is there any other documentation you're aware of on Sync Fx tracing?  It would be interesting to see what the output of a trace looked like to see how useful it would be in our scenario.  

    So if I understand your response correctly, you're saying that because there hasn't been any updates to the "SQLCEClientSyncProvider " there won't be any device side performance benefits to moving to SQL Server 2008.  Is that right?

    Ted Theodoropoulos Acrowire, LLC http://www.acrowire.com
    Monday, June 13, 2011 2:42 AM
  • if you're bottleneck is on the device, i dont see any significant performance improvements by simply upgrading the database server. it might improve performance on the server side, but not necessarily the overall performance of the sync topology.

    Also, in reality, you are not gaining any new functionality in Sync Fx 2.1 as well since you are using the older SqlCeClientSyncProvider/DbServerSyncProvider combo.

    the link you found is the most definitive doc you'de find on tracing.

    "We are not syncing any tables that don't need to be synced." - so are you manually adding only tables that has changed in the Sync Group?

    by default, Sync framework will loop thru all the tables in the sync group regardless of whether there was a change in that table or not. the link above shows an approach of determining what table has changes and only including those tables in the sync session. (e.g., if youre sync group has 100 tables and only 5 has changes, you only sync 5 tables vs checking all 100 tables for changes)

    Monday, June 13, 2011 3:05 AM