Stored proc behavior in different subscribers

  • SQL Severe DBA

    Hall of Fame

    Points: 3579

    We have 5 subscribers with same version of data and objects . However in two of the subscribers suddenly one of the  proc start taking long time .

    Interesting fact as soon as we run update stats on of the underlying table the stored proc works fine(executes in 1-2 seconds)  for 4 hrs and then again it starts taking 4-5 seconds to execute and higher logical reads .

    Any one experienced such kind of issue or want to suggest any possible solution to fix this .

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4479

    I sure hope you have SQL 2016 or higher and have the query store enabled for that DB.

    If that is the case, you answer can be found there. The QS will possibly show you different query plans, but it might also be parameter sniffing, or an tipping point index issue, or just growing pains due to more users accessing overlapping data and blocking each other.

    But solving issues starts by gathering data (QS, ext events, app logging, etc) and look for that what is different from the norm.

    grtz,

    Theo (NL)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply