SQL Server performance issue.

Posted by Jit on Stack Overflow See other posts from Stack Overflow or by Jit
Published on 2010-05-25T22:27:27Z Indexed on 2010/05/25 22:31 UTC
Read the original article Hit count: 300

Filed under:
|
|
|

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about Performance