SQL Server Performance

Transactional replication scheduling

Discussion in 'Performance Tuning for SQL Server Replication' started by boutwater, Oct 20, 2003.

  1. boutwater Member

    I am having performance problems ever since I published a transactional replication. I have tried a lot of the tips on the pages from this website, and they haven't solved the issue. It seems like for the most part, the performance is fine, but then every couple of minutes it becomes slow. Does anyone have any suggestions. Thanks,
    Ben
  2. satya Moderator

    For the slow performance running PROFILER would be the first point to assess the situation, may try using it.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. boutwater Member

    What would be a good thing to monitor. Is there a standard template that you recommend, or just create my own. Thanks for all your help,
    Bem
  4. Luis Martin Moderator

    Start with:<br /><br />From Store Procedures<br />RPC:RPC Completed<br />SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />mtmCompleted<br /><br />From TSQL<br />SQL:BatchCompleted<br /><br />Errors and Warning<br />Missing Column Statistics<br /><br /><br /><br /><br /><br />Luis Martin<br /><br />...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. <br />Bertrand Russell
  5. satya Moderator

    Apart from the standard template for PERFMON counters follow the below extract from Transaction replication tips on this website
    If you are using transactional replication, you may want to monitor the latency that it takes the Log Reader to move transactions from a database's transaction log until it puts it in the distribution database, and to also monitor the latency it takes the Distributor Agent to move transactions from the distribution database to the Subscriber database. The total of these two figures is the amount of time it takes a transaction to get from the publication database to the subscriber database.

    The counters for these two processes are the: SQL Server Replication LogReader: Delivery Latency counter and the SQL Server Replication Dist.: Delivery Latency counter.

    If you see a significant increase in the latency for either of these processes, this should be a signal to you to find out what new or different has happened to cause the increased latency.


    And also this MSDNhttp://msdn.microsoft.com/library/d...y/en-us/dnsql2k/html/sql2k_replperf_tran4.asp link explains the in and out about trans.replication.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page