SQL Server Performance

Transactional Pull Replication - Perfroamnce Issue with Distribution

Discussion in 'Performance Tuning for SQL Server Replication' started by mphasis, Oct 8, 2008.

  1. mphasis New Member

    Hi,
    I have a pull replication. The distributor and subscriber are on one box.
    For the first 2 days, I was getting great perfroamnce in Distributon. After that the cmds/sec has droped to 20cmds/sec to 80 cmds/sec.
    Earlier it used to run at 400 cmds/sec.
    Currently the log reader works well.
    I am seeing the issue with Distribution from distributot to subscriber. Is there any thing that can be done here.
    The number of transactions, cmds, etc is nearly the same on a day to day basis.
    Let me know your inputs.
    Thanks.
  2. ndinakar Member

    Is the distribution database getting bigger? Run the Cleanup agent and see if it clears up some transactions that have already been applied. The distribution agent reads the transactions from the distribution db and applies them on the subscriber. If you look through the tables, there is only one index (or may be two) on those tables. Perhaps cleaning up some old transactions might help. Also, check if your subscriber needs any maintenance.
  3. Saurabh Srivastava New Member

    Questions:1) How did you determine that distributor is bottleneck? How bad is replication latency?
    2) What is resource utilization on the subscriber system?3) How much data is being pulled? Is there any baseline number that lead you to conclusion that Distributor is now ill and unable to perform better?
    4) Where distributor data files are placed? Shared with user and system data files or separated?
    5) While poor performance on distributor database what waittypes do you see on a regular basis in sysprocesses? Suggestions: First, determine the root cause of this problem. Capture Replication perfmon counters during peak periods along with Memory,CPU and disk counters that will help you to ensure that system resources are not bottleneck. Once that is confirmed find out statements (see below) waiting to be replicated to check for poor query performance, verify that indexes on publisher and subscriber tables are similar and subscriber indexes are not fragmented.
    1) Run this command to find out the number of commands waiting to go to subscriber;
    SELECT AGENT_ID FROM MSDISTRIBUTION_STATUSGROUP BY AGENT_ID HAVING SUM(UNDELIVCMDINDISTDB)>100ORDER BY SUM(UNDELIVCMDINDISTDB) DESC
    2) Find out which commands are waiting to be loaded. SELECT TRANSACTION_TIMESTAMPP FROM SUBSCRIBERDATABASENAME..MSREPLICATION_SUBSCRIPTIONS
    3) Then run this in Distribution database (Replace value with one returned from above) SP_BROWSEREPLCMDS @xact_seqno_start=’ ‘
  4. Saurabh Srivastava New Member

    If your clean up agent jobs are running on default values/timings just check if there is any blocking caused by them.

Share This Page