SQL Server job taking longer

[rzirpolo]

I have a job setup on SQL Server which basically runs a very big stored procedure. This normally takes 2-3 mins to complete but over a certain period it was taking 20 minutes. I have two backups of data (one when the procedure was only taking 2-3 mins, the other where it was taking 20 mins).

I have struggled to find the cause of why this happened. I have checked the following,

1) Checked if there were more rows of data to be processed. There were only 29 more rows to process, so doesn't look like that's it.

2) I have checked what was happening on the SQL Server in regards to other jobs running in this period (sysjobshistory), and also the logs in the Event Viewer. There is nothing to prove this point.

3) One other thing I could think of was in regards to the stored procedure cache. If this had been cleared the procedure would have had to re-compile and the procedure would take considerably longer to run. I queried the syscacheobjects table on the master database but seeing as no date is associated to these entries it is impossible to determine when they were created.

I know this could be caused by network\connectivity issues. I just want to ensure I have checked everything possible on the SQL Server side.

Any tips or advice on what else I could check for would be a great help.

 

[WayneRyan]

r,

Did you lose an index?

Wayne

 

[rzirpolo]

In regards to Indexes, how could I tell ? And if it was lost would it have been re-created automatically by SQL Server ?

The reason I ask the second question is that the job went back to normal after.