SQL Server Performance

Performance Critical - Merge Replication

Discussion in 'SQL Server 2005 Replication' started by sql_jr, May 23, 2007.

  1. sql_jr New Member

    The replication monitor is showing a warning for Performance Critical on the subscriptions tab. What does this mean, and how can I troubleshoot this. All rows on all tables appear to be in synch (except one table) I also updated one table to test, and it was replicated over - but still have warning. Please advise!
  2. satya Moderator

  3. sql_jr New Member

    thx for reply....I'm not sure what I am seeing is expected (admit a bit weak on repl monitor). I also reinitialized the subscription last night successfully.
    On subscription -->synchronization history-->sessions of the merge agent, the status is running and duration appears to be 5.09:36:26 - Is this simply a running uptime of the agent since it started? I see no change in the window 'Articles processed in the select session' and status bar shows 'download 89% complete (approx 1:25:33 remaining)', and hasn't changed However, the error msg bar does always change to reflect the current status.

    Also, is it a good idea to change the merge option to 'High volume server to server'? Many thanks for your help! Please address these Q's
  4. satya Moderator

    What are the thresholds setup on yoru server?
    BOL
    quote:
    Falling short of processing the specified number of rows in a given amount of time.
    This corresponds to the options Warn if rows merged per second for dialup connections is less than the threshold and Warn if a merge length for LAN connections exceeds the threshold. Both of these thresholds can be set, but only one is used during a given synchronization. The Merge Agent applies the appropriate threshold based on the connection type.
    If the specified threshold is met or exceeded, the subscription status is displayed as Performance critical (unless an issue with a higher priority needs to be displayed).

    When you enable a warning, you also set a threshold. For example, if you enable the warning Warn if a merge length for LAN connections exceeds the threshold, set the maximum allowable length of time for a merge synchronization.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. sql_jr New Member

    Guess it depends which one I set. If I check "Warn if a Merge Length for LAN connections exceed the threshold" is set at 3 minutes.

    Can 'High volume server to server' help performance? THX a million!
  6. satya Moderator

    Theoriticall yes,because merge replication uses triggers, and although still not as efficient as transactional replication, merge replication is noticeably faster in SQL Server 2005 when replicating changes between computers that are connected by a fast reliable network.

    Unlike transactional replication, merge replication by default detects and resolves conflicts, making it an appropriate choice when the replication environment can have conflicts.


    So asper the Business requirement if you have chosen Merge Replication, it is important to understand that performance and scalability are dependent on good database and application design and the appropriate setting of replication parameters. For additional information about performance tuning, see the section "Enhancing Replication Performance" in SQL Server 2K5 Books Online.


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page