April 22, 2008 at 4:38 am
Hi all,
We have a couple of overnight batches running on sql server 2005. Some are vb6 apps talking to sql server via ado, and some are simply stored procedures.
We record the completion times for each day, and we noticed something very strange : each day the batch completion times go up in a linear way. A batch that took exactly one hour on the first run, takes an hour and 15 minutes to complete after 3 months. At first, we thought this was simply because over time, the database collects more data.
Then this happened : for some reason, they restarted the sql server service at the customers sql server ( the service had been running for 3 months ) Batch completion time after the service was restarted : One hour!
Your first thought is probably "statistics", but the state of the indexes and statistics of this database are constantly maintained. I'm not ruling out any statistics problems, but i'd like to hear what other possible causes there could be.
April 22, 2008 at 4:49 am
Maybe SQL Server is using an out-of-date execution plan. You can use Profiler to capture the execution plan and duration of a stored procedure. If it increases as before, use sp_recompile to force the stored procedure to use a new plan and then see whether the new plan is the same as the old one.
John
April 22, 2008 at 7:19 am
We're going to try it and see how it affects the curve. I'll let you know if that was the problem.
Thanx!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply