August 24, 2009 at 4:11 pm
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?
August 26, 2009 at 2:50 pm
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.
August 26, 2009 at 6:54 pm
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
August 26, 2009 at 7:42 pm
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
August 27, 2009 at 9:17 am
Thanks guys, I already found the solution here:
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