locked
scope-level TRANSACTION make performance very low RRS feed

  • Question

  • Hi,

         I divided my data which need to be synced into serveral scopes, like the official docs suggested, the rule is based on my business, like there's one scope named 'SalesTrxScope' which for saving all real-time running business data and also referenced by report module, this scope contains nearly 20 big tables(each table contains 1 million rows) and 5 new rows(most of rows size are >8K) inserted into each table per 1m when business is running.    our sync interval for this scope is 1.5m, this is required by business.

         Now the problem occurs,   from the sync logs, each sync cycle cost over 300 seconds to select and apply changes in a LAN enviroment, as I know, the sync framework make the scope as a TRANSACTION, so easy to understand, the update lock will be set to table for 300 seconds, this is the disaster for our business, these tables can't be inserted any more becasue some other logic need to put a X table lock, the business blocked by this bad performance with lots of timeout exception in App, this is really headache for me, but from the developer angle, these tables are an atomic unit for the business, I can't seperate them any more, or the data will not be consistent and cause report error.

            I tried everything I can do:  filter the synced column;adjust the sync interval(but not too much since the business requireent);table index re-organize...

    do you have any idea?     


    This is shawn.

    Wednesday, July 18, 2012 2:52 AM

All replies

  • have you tried enabling sync framework tracing in verbose mode to see where its spending most of its time? reading changes vs applying changes?

    if a sync cyle takes 300 seconds, and your sync interval is every 90 seconds, are you firing concurrent sync sessions?

    am not sure the sync cycle equals the amount of time the locks are held.

    and i dont think its fair to blame scope level transactions when you're application is timing out because it requires an exclusive table lock. you require a sync every 1.5 min and you have an application that requires an exclusive lock at the same time. so you want two concurrent processes to coexists with one of the processes requiring an exclusive lock?

    Wednesday, July 18, 2012 6:49 AM
  • 1.  We always experienced a select change timeout on one table which is the biggest one in our database, the exception like:

    Cannot enumerate changes at the RelationalSyncProvider for table 'biggestTable'.  Check the inner exception for any store-specific errors. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    2.   We never run concurrent sync sessions.

    3.   when doing the syncing, our business still need to run, so I think this is one you mentioned of 'concurrent processes', I don't think we can get ride of this case since it's required by our business.

    thx.


    This is shawn.

    Wednesday, July 18, 2012 9:00 AM
  • 1. There is a CommandTimeout property that you can set to work around this issue.

    3. Your scenario regardless of whether you use Sync Framework or not will still be an issue. assuming you use SQL Replication, SSIS, or a custom app, the issue of concurrency with an app that requires exclusive lock remains.

    Thursday, July 19, 2012 2:09 AM
  • the point 1, we did consider to set a bigger timeout for select change, but meanwhile, it'll extend the table locking time and further block other process.   it's have to find a balance.

    whatever, it may never have answers for my problem now, let me try from other angle.

    thx.


    This is shawn.

    Saturday, July 21, 2012 2:02 AM
  • como saliste de este problema??
    Tuesday, November 13, 2012 7:37 PM