August 17, 2004 at 12:03 pm
I have a job that is scheduled to run each night. Most nights it take 5-10 minutes to complete. Every now and again, it jumps to 100+ minutes to complete. As it's scheduled to run during off hours, that's not a problem. What bothers me is I cannot figure out what causes the time to jump so drastically. Any thoughts on how I can trap the information? It's a dedicated SQL 2K, sp3 server. Thanks.
Terry
August 17, 2004 at 12:22 pm
It sounds like a blocking issue. It could also be resource contention. Set up a job that runs frequently that monitors blocking. alert you when blocking happens.
August 17, 2004 at 2:12 pm
If you have couple of steps in the job then narrow down as which step is taking the time, how many time the retry option is set up in the job. It could be a possible the job failed but the retry option keep making the job
Amit Lohia
August 17, 2004 at 2:39 pm
What type of job is it?
August 18, 2004 at 3:07 am
I would suggest running the SQL profiler over night. If your script inlcudes storedprocedure calls then add from the events tab Storedprocedure:
RPC:Starting and RPC:Completed
SP:Starting and SP:Completed
SP:StmtStarting and SP:StmtCompleted
you will also want to add TSQL from the events tab SQL:BatchStarting and SQL:BatchCompleted.
Finally add from Locks: Lock:Timeout
Make sure you have the duration column selected on the data columns tab.
When you run this profile it will generate a large quantity of data but will allow you to determine which parts of your script are running slowly (the duration column) Also if there is an issue with locking then Lock timeouts may show somthing useful.
You may also want to set a filter so it only caputures data from a particular host but you may miss some other activity that is affecting your process.
Regards
Julian
August 19, 2004 at 7:44 am
What type of load will profiler add to the system?
Terry
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply