SQL Server Performance

Replication Performance

Discussion in 'Performance Tuning for SQL Server Replication' started by madduri, Jan 17, 2007.

  1. madduri New Member


    I am creating 1000 records in a database through a stored procedure. These are replicated to a different database in the same server. The replication process is taking almost 2 hrs to transfer all the data. I have changed the querry time out value to 10000, which I think is high enough value and changed the commit batch size value to the no. of transactions (1000) in the agent profile. But there was no change. In between the replication gives a message saying 'the process is running and is waiting for one of the backend connections'. Is there any solution for this issue. Kindly suggest a solution to speed up the replication process. It is transactional replication.


  2. madhuottapalam New Member

    you are replicating to a database on the same server and it is taking 2 hrs to complete 1000 records. of course there is something terribly wrong in your setup. I have administered Transactional replication for 3 years and i have seen millions of records replicating within few minutes in same domain and different server. So tell me, is it immediate updating or queued updating. and post the full error message with severity and error message no. Explain full architecture.

  3. ghemant Moderator

  4. MohammedU New Member

    As Madhu mentioned it is really something wrong...
    I am doing the replication coast to coast with few seconds latency.

    First thing I will check the blocking on the server before doing anything.

    Mohammed U.
  5. madduri New Member

    Hi all,

    Thank you for your replies. It is immediate updating. I dont find any errors in replication. It keeps running saying 'the process is running and is waiting for one of the backend connections' and 'delivering replicated transactions'.
  6. madduri New Member

    I do not find any blockings too.
  7. madduri New Member

    I have updated the statistics and had rebuild the indexes...still the same. Please suggest some solution.

  8. mdefehr New Member

    The only thing I can suggest given all the unknowns is to create a simple test table and replicate it - assuming you can make it perform well with a simple case, make it more and more like your production case until you start running into problems - presumably, the solution will present itself

  9. MohammedU New Member

    Is replication using default profile or user profile...Can you post profile info..

    Mohammed U.
  10. madduri New Member

    Hi Mohammed,

    I have created a user profile which is being used as below.

    Distribution Agent:

    BcpBatchSize: 100000
    CommitBatchsize: 1000
    CommitBatchThreshold: 10000
    HistoryVerboseLevel: 1
    KeepAliveMessageInterval: 300
    LoginTimeout: 15
    MaxBcpThreads: 1
    MaxDeliveredTransactions: 0
    PollingInterval: 10
    QueryTimeout: 60000
    SkipErrors: No Value
    TransactionsPerHistory: 100

    Log Reader Agent:

    HistoryVerboseLevel: 1
    LoginTimeout: 15
    PollingInterval: 10
    QueryTimeout: 60000
    ReadBatchSize: 1000

    Thanks, Madduri

Share This Page