none
SP's Slow Using Replication RRS feed

  • Question

  • Hi all!

    I've configured an Database Replication Snapshot between 2 servers, Server1 is publication, Server2 is subscription.

    Before initial subscription, I restored an backup of database from server1 to server2, and test all querys, that was work fine, directly querys in tables and Stored Procedures.

    But, after initial subscription, SP's are slow, take about 10 minutes  to return data, before, the same SP returns in 1 second.

    Anyone knows what happen? Why only SP are slow?

    Both server's runs SQL 2017, Win 2019 and database is in 2008 compatibility. 


    Douglas Filipe http://douglasfilipe.wordpress.com

    Thursday, April 30, 2020 3:18 PM

All replies

  • What you describe does not have anything to do with Replication.  Replication is an async process and cannot in any way affect running processes.

    What you describe is almost always a problem with "parameter sniffing".

    Please see:

    https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

    Thursday, April 30, 2020 3:45 PM
  • Hi Douglas Filipe,

    >> Anyone knows what happen? Why only SP are slow?

    Please try to follow the blog to troubleshot Stored Procedures slow issue. Hope this could help you.

    How to Start Troubleshooting a Slow Stored Procedure

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 1, 2020 8:12 AM
  • Hi Douglas Filipe,

    Any update? If the reply could help you, please mark the useful reply as answer.This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 4, 2020 6:39 AM