Slow SQL Server Agent job

  • Hi, I have an issue with the duration of a job I created for a stored procedure, when I execute this SP by itself it takes at most 2-3 seconds to complete, but when part of a job that executes this SP every minute it has a 8 minute average duration; I've tried stopping all other jobs but this didn't change anything.

    Anyone has a clue on what might be the issue?

  • What error messages in the log's do you see? That would be a place to start. Also, look at performance monitor, Are there net work issues: I/O, batch locks. Error message will help in starting to find the needle in the haystack.

  • Being able to answer this question without more information is like taking a shot in the dark.

    Some of the things you might look for are to see if other users might be causing resource contention when the job is executing.

    In addition, code, dml and sample data are extremely helpful in helping you to get a faster answer.

    Please refer to the first two articles in my sig. block. They will help you get some of the things we need here to better help you more quickly.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Google parameter sniffing - sounds like that might be what you are running into.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks guys, I already found the solution here:

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/cf0b0fb5-2681-4036-8bb3-e5e0c68a1ff8

    Someone mentioned removing PRINTs from the stored procedure and that turned out to be the problem.

Viewing 5 posts - 1 through 4 (of 4 total)

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