SQL Server performance issue.

Question

Hi Friends,

I have been trying to analyze performance issue with SQL Server 2005. We have 30 jobs, one for each databases (30 databases, one per each client). The jobs run at early morning at an interval of 5 minutes.

When I run the job individually for testing, for most of the databases it finishes in 7 to 9 minutes. But when these jobs run at early morning, I see few jobs taking 2 to 3 hours to finish and the same takes few minutes as mentioned above if ran independently. We dont have any other job scheduled during that time, other than these 30 jobs.

If we restart the server then for 2 or so days all the jobs finishes in few minutes, but over the period of time (from 3rd day suddenly), few jobs start taking hours to finish. What could be the possible reason of performance degradation over the period of time?

I verified all the SPs and we uses temp tables and I made sure none of the temp table is left without dropping at the end of SP.

Let me know what are the possible reasons for such behavior.

Appreciate your time and help.

Thanks

Answer 1

Not much to work on from your description but..

You say each job takes 7-9 minutes when run independently but you are starting them at 5 minute intervals. So, from the 3rd job onwards they are going to be overlapping?

What do the jobs do? Are there potential points of contention e.g. do two or more jobs act upon the same tables?

Have you logged any performance monitor counters when the problem occurs? Do you have Profiler traces? Have you checked for blocking queries?