none
SQL job issue RRS feed

  • Question

  • We are running into a wierd issue. If we run a stored procedure within a SQL job is running slow. But if we run the same stored procedure from SQL Server Management studio query window, it runs faster as expected. Did anyone run into this issue? Any pointers to troubleshoot this issue? We have SQL 2016 Enterprise edition. 

    Thanks.


    sqldev

    Tuesday, April 16, 2019 9:13 PM

All replies

  • It doesn't matter if a SP is executed manually or by a Job, the execution is always the same. Possible reason is, that on the time of execution by Job the SQL Server is more busy or there are locks on the table(s), which slows it down.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, April 17, 2019 6:19 AM
    Moderator
  • We are running into a wierd issue. If we run a stored procedure within a SQL job is running slow. But if we run the same stored procedure from SQL Server Management studio query window, it runs faster as expected. Did anyone run into this issue? Any pointers to troubleshoot this issue? We have SQL 2016 Enterprise edition. 

    Thanks.


    sqldev

    are you sure you're passing same set of parameters in both the cases?

    Another reason can be schedule of the job i.e it might be running during a busy period

    Yet another case can be schema /db against which its executing. job might be executing the procedure in a different db or under different schema where data volume or proc definition itself might be different. So you need to first make sure all conditions are "identical" between both the scenarios


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, April 17, 2019 6:39 AM
  • check any open transaction or blocking while job running from sql agent.

    There will be no difference both execution.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Wednesday, April 17, 2019 6:43 AM
  • Check if ARITHABORT is ON when run with the job.
    Thursday, April 18, 2019 6:15 AM
  • Yes, this is a popular phenomenon, and there is one more than possible explanation, although most often it is a combination of SET options and parameter sniffing.

    I discuss both the possible causes and possible remedies in this article on my web site: Slow in the Application, Fast in SSMS?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 18, 2019 9:43 AM