SQL Server Performance

Transactional replication performance

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

  1. madduri New Member

    Hi,

    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.

    Thanks,

    Madduri
  2. satya Moderator


    I believe you need to take care of other processes while running this update using stored procedure, make sure that SP is compiled and all associated tables stats are updated for optimum performance. Why not you perform the update in smaller batches in order to keepup the performance.

    Refer tohttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18949 and please do not post multiple(duplicat) posts for same problem.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page