December 4, 2006 at 4:05 pm
I'm hoping someone out there can help with this one. I have a performance issue with one of my SQL jobs. The SQL job runs once per hour, and normally takes about 30-40 minutes to complete. The job runs fine for several weeks, and will suddenly spike in run time length (from 30-40 minutes to 2-8 hours). The spike is immediate, and not gradual, and does not occur at the same time of day, or even the same day of the week. Once the job runtime spikes, it will continue to run with completion times of 2-8 hours. In other words, it will not return to norm state after it has spiked. Recycling SQL Server resolves the issue, returning the run time to norm state again. We have tried several things, including disk defrag of the database and log file disks, looking at server memory utilization (only about 50% utilized when issue occurs), and using the DBCC DBREINDEX command to rebuild indexes once a week. I am running SQL 2000 w/ SP3 on a Win2K Adv. Server w/ SP4. The server has 4GB of memory, with 36GB SCSI disks in raid mode. The job does several things, including selecting from a SQL table into a table variable, using a cursor to loop through the table variable, and updating records in several other tables. This problem has been occurring for over a month, and I am out of ideas at this point. Any help would be greatly appreciated.
Dave
December 4, 2006 at 7:49 pm
Recycling resolve the issue means, it could be related to cached query plan. Instead of recycle try to recompile the underlying procedure for the job OR alter the procedure to add with RECOMPILE option...
AND/OR
May sure update stats is enabled on db or add the update stats for tables involved in the job as first step.
MohammedU
Microsoft SQL Server MVP
December 5, 2006 at 4:31 am
Yup sounds like a poor plan to me, however, you mention table variable - these are good for small datasets - you say loading a table into one and looping a cursor, I'd suggest you might want to look into using a temp table in place of the table variable. Without more details it's very diffiult to tell - I'd suggest extracting the query plans, you could do this through profiler so you'd be able to see the differences when your job slows down. You might want to check the query plan anyway.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 5, 2006 at 10:31 am
Thanks for the advice and suggestions. I will implement these and see how it goes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply