none
how to optimize transnational replication RRS feed

  • Question

  • in our production environment, we use transactional replication and its running slow and taking CPU and memory. 

    Could you please help me how to optimize replication?

    Thanks,

    Thursday, June 29, 2017 5:16 AM

All replies

  • Where is it taking up cpu and memory?

    If it is the publisher you need to disable immediate sync and ensure that your distribution cleanup task it keeping up at least several times a day.

    If it is on the subscriber you need to look at wait stats there.

    Thursday, June 29, 2017 2:43 PM
    Moderator
  • It is running slow on publisher DB server.
    • Edited by R987654 Friday, June 30, 2017 5:02 PM
    Friday, June 30, 2017 5:02 PM
  • Transactional replication uses an asynchronous log reader on the publisher.  This process takes very small CPU and memory.

    What EXACTLY are you looking at to determine replication is causing your CPU and memory activity?

    Friday, June 30, 2017 5:51 PM
  • The transactional replication agents (distribution and to a lesser extent) the log reader agent will consume significant CPU when msrepl_commands and msrepl_transations are large. Best way to fix this is to disable immediate_sync and ensure your distribution clean up task is running.

    use PublicationDatabaseName
    GO
    sp_changepublication PublicationName,'immediate_sync',false

    Friday, June 30, 2017 6:44 PM
    Moderator
  • Im assuming that you have local distributor? If so moving it off to remote one should also help.
    Monday, July 3, 2017 1:03 PM