SQL Server Performance Forum – Threads Archive
Transactional replication performanceHi, 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
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
This posting is provided AS IS with no rights for the sake of knowledge sharing.