August 20, 2007 at 9:12 am
a job that is supposed to run for 1H. It took 4 hours to run during the weekend. any way to find out what made it to take 4 hours to run?
It has never happened this before. Or maybe just once or twice during the last years.
August 20, 2007 at 9:42 am
Check ur CPU usage also check any other jobs or tasks are running at the same time.The issue can also be due to low disk space.
August 20, 2007 at 10:05 am
Does it only take that long over weekend or it got longer from now on?
As Ratheesh suggested, check if any other jobs conflict. I once had backup job run longer than usual - it overlapped with Optimization Job, resulting in the optimization job to run ~1.5 hours (when it usually runs for 2 minutes).
August 20, 2007 at 1:45 pm
One possible cause to the long running job is blocking. It may have been waiting for a resource for the majority of the time.
This could be extremely difficult to debug after the fact. I think your best bet would be to set up a trace, or do some type of custom error logging, like logging blocked processes to a table.
Regards,
Rubes
August 21, 2007 at 7:29 am
running slow barely occacionally in the last 3 weeeks.
August 22, 2007 at 7:42 am
It may be of some help if you could provide a little more information about what the job does. Are there any other job(s) executing at that time ? Were there any 'special' or one time jobs executing then ? Did some database maintenance fail on the night or two prior ?
perfmon could also be used to gather overall OS statistics in addition to those that SQL Server provides and log them as well.
As always more questions than answers to start with ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 23, 2007 at 6:39 am
1-what the job does.
It pulls data from a AS400, insert, delete, update.
2-Are there any other job(s) executing at that time ?
No, i run sql profiler, and i saw it was the only job running.
3-Were there any 'special' or one time jobs executing then ?
this job executes everyday at 3am it takes an hour to finish
4-Did some database maintenance fail on the night or two prior ?
no maintenance job failed. Maintenance job are run on the weekend
The code hasnt been changed. it was runnig normally most of the time..but the past 3 weeks its taking longer to run occacionally. Any help will be appreaciated. some tables dont have indexes. But that cant be the problem cause the job ran fine 3 weeks ago...
Perfmon average disk queue lenght exceeded 2 for more than 10 minutes. therefore, there is a I/O bottleneck. But the job ran fine 3 weeks ago.
August 23, 2007 at 7:04 am
Is there any chance that the problem is on the AS400 side?
August 23, 2007 at 7:26 am
I agree with Steve that it sounds like it may be related to the AS400 side of things. It could either be something running (or not) on the AS400 or it could be network related since the AS400 is probably in a world (subnet) of its own. One culprit I have found in the past is that if TSM is running on the AS400 ... your network connectivity and traffic can be severely impacted.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 23, 2007 at 2:47 pm
ASked the person in charged of AS400 Side. He told me AS400 is fine and nothing major has been changed.
Could not doing "Update statistics" decreate the performance?
August 24, 2007 at 7:58 am
Yes. Sometimes just hitting certain size/rowcount threshhold can case this. Another potential reason is a large volume of insert/update/delete activity on tables as well. When running UPDATE STATISTICS on your tables doi not forget to execute sp_recompile for each table that statistics has been updated on and sp_refreshview for all views except those with SCHEMA BINDING.
Back to the AS400 now - well there might not have been any apparent problem with the AS400 affecting the appearance of its normal function but there are a number of things that can really mess with your linked server reponse. Most of them are caused by the network. If the problem persists you might want to have your network folks monitor trasffic between your SQL Server and the AS400 and additionally minotor the AS400 for multicast traffic or broadcast storms. Both of these things can occur messing woth a windows network but seemingly leacve the base operations of the AS400 unaffected !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 25, 2007 at 10:19 am
Yes. Sometimes just hitting certain size/rowcount threshhold can case this. Another potential reason is a large volume of insert/update/delete activity on tables as well.
This SP hasnt been changed for a long time. It started to happen a month ago. It ran slow for 9 times since 06/24.
Back to the AS400 now - well there might not have been any apparent problem with the AS400 affecting the appearance of its normal function but there are a number of things that can really mess with your linked server reponse. Most of them are caused by the network. If the problem persists you might want to have your network folks monitor trasffic between your SQL Server and the AS400 and additionally minotor the AS400 for multicast traffic or broadcast storms. Both of these things can occur messing woth a windows network but seemingly leacve the base operations of the AS400 unaffected !
How do you explain me, the sp ran for 1 years without problems. using the same network connection?. Suddenly started to run slow on 06/24
August 25, 2007 at 11:40 pm
I think Rudy has some great ideas that deserve investigation. Most networks and most database systems have one thing in common... change. Just because everything was running smooth a year ago, or even a week ago, does not mean that they will today. We can only make guesses as to what changed in your environment to cause this bad performance. The key is monitoring current events.
Yes, the SP has not changed. But are the rowcounts on your tables the same? As Rudy pointed out, table sizes may have increased to a level that is causing the optimizer to react differently, creating a new query plan that is slower than a previous version. I've seen this happen many times on databases I've designed myself. Things often need to be tweaked as your data changes. Your indexing may need to be reviewed. Try running a trace on your sql job and see how many reads it's using up. Look at CPU before, during, and after the job runs.
Same thing goes with the suggestions to look at the AS400 side. Same network connection, but is it? Perhaps a configuration has changed, usage has changed, etc.
Are you absolutely positive that nothing is blocking your job? Here is a real live example that I've lived through... I come in to work at 8 am and the phone is ringing off the hook with users complaining about performance. I look at the sql jobs and find that my reindexing job is currently running. It was scheduled to run at 3 am and is normally finished at 4 am. Lo and behold, I see through my block monitoring that the reindexing had been blocked for hours on a transaction that had been left open.
Use the monitoring tools available. There are lots of them. I'd start with creating a trace to monitor all sql jobs.
Regards,
Rubes
October 9, 2007 at 10:18 pm
Thank you guys!
Found out other jobs are blocking this job. Change the execution time for the blocking job and it solved my problem.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply