job execution Time

i observed a strange problem in my production setup. i have a job which updates usage metrics (for reporting) which is scheduled to run once in a day. (the job invokes an sp to do this. the sp refers two tables to retrieve/update information, say TableA and TableB).

the job normally takes an average of 25 seconds to complete. all of a sudden the job execution time increased to 6 minutes and 52 seconds. now, the average job execution time is 8 minutes. there is no table/sp change in the DB

the only thing i observed is that one of the tables referred by the sp has 30,000 records added to it, on the day from which the job execution time increaed to 6 minutes.

i have updated the statistics on the Table, but the execution time remains unchanged. can any one suggest any possible causes for such a scenario.

i expect a few hints with which i can explore my production DB and find out the causes for the increased execution time for the sp.

Pl discuss...

Thanks in advancethe only thing i observed is that one of the tables referred by the sp has 30,000 records added to it, on the day from which the job execution time increaed to 6 minutes.


I think you may have answered your own question! If the columns that your stored procedures are referencing are not indexed, they will likely have to perform table scans. You mentioned that 30,000 rowd were added...how many did you start off with.

Have you tried generating an execution plan using Query Analyzer? That will give you a good indicaton of where the problem lies.