May 21, 2015 at 2:48 pm
Hi,
I have a couple of SSIS jobs setup by someone else which run roughly every three minutes to copy updates from a source database to a reporting database.
The jobs tend to run very quickly for a number of hours (used to be days but now is hours), and then performance becomes very poor.
When they are running "correctly", one job will take about a minute and a half and the other about two minutes, they then repeat. However, recently they only run for a few hours and then the jobs start taking 45 minutes, an hour, two hours, and then eventually just hanging - SQL Server thinks they are still running but after 8-10 hours, nothing is happening.
Potential item of note, the servers (dev & prod) are both VMWare servers and not actual, stand-alone servers.
I am not SSIS knowledgeable and so am looking for insights on potential causes for why it would run quickly for a while and then start slowing down.
Rebooting the server resolves the problem for a while but continually rebooting the server is not an option.
Thanks for any thoughts you may have.
Regards,
Mark.
May 21, 2015 at 3:13 pm
It's hard to guess without looking at what your SSIS packages are doing or seeing the changes in your servers' state throughout the day. Also I'm no SSIS guru but here's a few things that I have seen and/or would look for.
On the SSIS server side - If available memory is decreasing throughout the day that would certainly create the kind of behavior that you are seeing. This is the kind of thing you could inspect with perfmon. You could also inspect the event viewer for memory leaks.
A common cause of "gradual slowing down" is when your log or data files are growing. I have seen environments that do transaction log backups throughout the day. They don't experience log auto growth in the morning because there are not as much activity but as activity increases so does the log auto growth. Auto Growth is a killer. Google sp_blitz - that's a good tool for identifying problems like that.
Rebooting the server will fix memory problems, reset your tempdb, stuff like that. If you can figure out what rebooting is fixing you can likely solve the problem without rebooting 😉
Just a couple ideas, I'm thinking out loud.
Edit: added note about rebooting.
-- Itzik Ben-Gan 2001
May 21, 2015 at 3:19 pm
Thanks Alan,
I the primary DBA and myself have checked the CPU usage and memory and both appear to be good.
I just tried restarting the SSIS Service and it had no effect. Follow up to that, I tried restarting the SQL Server Agent Service and the problem gets resolved.
Perhaps the problem is not SSIS but SQL Server Agent? Come to think of it, that is likely where the problem resides as the jobs in question have multiple steps - some of them SSIS and others T-SQL. The performance is universal whether the step is SSIS or T-SQL.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply