none
Job running slow and showing unexpected behaviour

    Question

  • Dear Folks,
    I am troubleshooting a job which takes sometimes 2-4 hours to complete, sometimes completed in 9 mins and 25mins respectively on some other day.

    In this job several stored procedures are running. I found a stored procedure with many updates command running. at that time job runs slow.

    When I executed sp_who2 active command, I found update query running on database with CPU time = 55391 and DISK IO= 248043
    When i dig more  I found that disk IO should not be more for good performance.

    I also collected that PAGEIOLATCH_SH=30ms.

    When I googled I found PAGEIOLATCH_SH should be less than 20ms for better or good performance.

    Apart from this PAGEIOLATCH_EX =8ms which I thick is better. I also found some missing indexes on few tables.

    I checked Memory also
    Total Memory = 86 GB
    Min Memory = 2GB
    Max Memory = 40GB
    MDOP =4

    Need your opinion that what I should do to tune the performance when this job runs?


    Regards,
    Yashwant Vishwakarama


    Friday, February 02, 2018 9:31 AM

Answers

  • Those all values you calculated are cumulative and hardly has any significance. The question is quite open ended and with stored procedures I would check parameter sniffing or blocking. 

    You must look at each procedures instead of looking whole job. I would put getdate () befor and after each proc and see how much time each is taking then go ahead and start working on the one which is taking most of the time.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 02, 2018 10:42 AM
    Moderator

All replies

  • Those all values you calculated are cumulative and hardly has any significance. The question is quite open ended and with stored procedures I would check parameter sniffing or blocking. 

    You must look at each procedures instead of looking whole job. I would put getdate () befor and after each proc and see how much time each is taking then go ahead and start working on the one which is taking most of the time.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 02, 2018 10:42 AM
    Moderator
  • Thank You Shanky :)

    Will go ahead with your suggestions.

    Friday, February 02, 2018 12:24 PM
  • Hi Vishwakarma Yashwant,

    I'm writing to follow up with you on this post. Is this issue resolved? If so, please mark Shanky's reply as answer so that other forum members with similar issue can benefit from it when they this thread. If not, please provide more information so we can have better understanding.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    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, February 09, 2018 7:14 AM