none
sync services 2.0 and data trigger performance RRS feed

  • Question

  • I am using sync services 2.0 and needed to ignore the data trigger provisioning when applying a scope and create the update/insert/delete data triggers myself so I could set SET NOROWCOUNT ON on these triggers.  The sync provider that I am using is a SqlSyncProvider.

    I have about 35 tables that we are tracking in this scope, and now I'm getting poor performance within my application upon updates and inserts etc.  Namely, this is due to these data triggers.

    Are there any posts about tweaking and enhancing the performance of the data triggers? 

    I have searched the internet for asynchronous triggers with sql server, and most posts say in order to implement this functionality you should use a service broker.   This page has an example:

    http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

    Has anyone needed to implement this functionality in order to improve their performance?   Thanks in advance for your feedback.
    Tuesday, January 26, 2010 4:58 PM

Answers

  • Would you please use SQL profiler to listen to the SQL server to catch the difference between when the trigger was disabled and enabled?
    If you think this performance degradation is caused by the side table operation from the insert/update triggers, then would you mind checking whether index(es) are build on the user tables and side tables (after the provisioning)?

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, January 27, 2010 1:49 AM
    Answerer

All replies

  • Hello -

    Usually adding DML triggers to a user table will have certain performance impact on the regular DMLs.  So let me try to clarify your scenario, if you do not  mind.

    1. The Sync App has 35 tables in one Sync Scope object.

    2. After the provisioning, the INSERT/UPDATE trigger does NOT have "SET NOROWCOUNT ON" in the trigger declaration.
        In this case, you observed that the daily DML encounters some performance issue.
        How bad is the performance?  Do you have the slowdown in percentage?

    3. Then I assume that you modified the trigger to add "SET NOROWCOUNT ON" to it and did you see any
        performance improvement from #2 on regular DML?

    In general a user can modify the DML trigger created by the provisioning process, to improve its performance.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, January 26, 2010 6:32 PM
    Answerer
  • Here's are some of my replies to your questions:

    1. The Sync App has 35 tables in one Sync Scope object.

    - Yes basically i want to keep all my tables synced, and right now there are about 35 tables.  This number will grow in the future.

    2. After the provisioning, the INSERT/UPDATE trigger does NOT have "SET NOROWCOUNT ON" in the trigger declaration.
        In this case, you observed that the daily DML encounters some performance issue.
        How bad is the performance?  Do you have the slowdown in percentage?

    3. Then I assume that you modified the trigger to add "SET NOROWCOUNT ON" to it and did you see any
        performance improvement from #2 on regular DML?

    - The default triggers that get created by using the provisioning do not include the SET NOROWCOUNT ON.  So I had to alter the  triggers to include this  statement.  I cannot compare the system with triggers without the SET NOROWCOUNT ON because I have an ORM in which its entities need this in order for
    the triggers to play nicely.  But comparing the system before applying the triggers and after applying the triggers, I would say the system has slowed down on update/inserts about 30-40%.


    In general the triggers are working correctly and updating the tracking tables, and syncing works.  My major problem is that it has slowed down my database performance.

    I hope this clarifies some things.

    Thanks for your reply
    Tuesday, January 26, 2010 7:53 PM
  • Would you please use SQL profiler to listen to the SQL server to catch the difference between when the trigger was disabled and enabled?
    If you think this performance degradation is caused by the side table operation from the insert/update triggers, then would you mind checking whether index(es) are build on the user tables and side tables (after the provisioning)?

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, January 27, 2010 1:49 AM
    Answerer
  • Thanks again for the reply. 

    Real quick, I did check the tracking tables and the correct keys had been built.

    I will check with the Sql profiler and let you know my results.

    Thanks.

    P.s. By the way I said I used  SET_NOROWCOUNT ON  on my triggers early.  Small typo here it should be SET NOCOUNT ON.
    Wednesday, January 27, 2010 4:01 AM
  • After looking at the database activity with sql profiler, I saw that my inserts into the database were taking an inordinate amount of time to complete.  I analyze the joined statement in some of my insert triggers  and realized that the join was the problem and this initiated sql server to do a clustered index table scan.  So in essence each time an insert was happening it was scanning the whole tracking table looking for indexes that matched.   I rewrote the insert trigger to optimize the join on the tracking table, and now I have great performance.  

    Again, I was using the provisioning built into the framework 2.0.  I guess the moral of the story here is to check your triggers and adjust accordingly if you have poor performance.   Thanks for all your replies and help.
    Thursday, January 28, 2010 8:10 PM
  • I am having the same issue with the trigger generated. Since the trigger do not contain (and even there is no mechanism to enable/disable this) SET NOCOUNT ON it return value which gets conflicted with my original code. Due to this all my business has broken. Did someone come across such situation?
    Monday, September 13, 2010 1:17 PM