Dear friends,
Our package which at the time of normal execution takes 2-2:30 mins for fetching data on VPN with some select queries. But some times its job runs for hours and hours. What could be the exact reason behind it I guess its queries are stuch somewhere, but not when we run from the BIDS or run the job manually.
Please help. Thanks.
Have you checked the status of the query Perhaps do something like sp_who2 to see if teh queries are really active still. This assumes teh queries runa against SQL Server.
Do you monitor the VPN, both speed and connections down it Maybe the VPN dropped out and had to re-negotiate and this broke the connection but left in thinking it was still there The VPN may perform fine during testing but you monitor it constantly so when this happens again you can review the logs.
What are the differences between when the packages run quickly or take a long time
Do you capture any audit data from your packages so you know the number of records extracted, and loaded into the target database (If you do not, then it will be difficult to solve your problem.)
Are there any patterns (only in the morning, only on the 7th Tuesday of a month, that sort of thing) around the problem behavior
In addition to looking at SSIS and the source and destination systems, you should probably also look at the core four resources monitored when performance tuning: Memory, Processor, Network and I/O. If one of these resources was being over-utilized on one of the core machines used by your package, but only sometimes, this could explain y our behavior.
Also, the fact that you're using a VPN and not a LAN connection is what I would probably focus on first. Pulling lots of data over a slow connection can be problematic.
Good luck!
DarrenSQLIS wrote:
Maybe the VPN dropped out and had to re-negotiate and this broke the connection but left in thinking it was still there The VPN may perform fine during testing but you monitor it constantly so when this happens again you can review the logs.
Thanks for the help. I guess this as the biggest possibility. But where to check logs you have mentioned
Do you capture any audit data from your packages so you know the number of records extracted, and loaded into the target database (If you do not, then it will be difficult to solve your problem.)
Around 5000 rows are coppied with each package execution to the destination. But the total lines or source tables will be around 50k.
Are there any patterns (only in the morning, only on the 7th Tuesday of a month, that sort of thing) around the problem behavior
Mostly around US daytime, also the source server is busy with updates are being made.
Also, the fact that you're using a VPN and not a LAN connection is what I would probably focus on first.
MatthewRoche wrote:
Not very sure about this, how to monitor It runs well if I manually run the job.
Thanks.
MatthewRoche wrote:
Also, the fact that you're using a VPN and not a LAN connection is what I would probably focus on first.
Not very sure about this, how to monitor It runs well if I manually run the job.
Thanks.
There are many tools out there, free or not free, that can be used for performance and network monitoring. I'd start with Performance Monitor (http://www.windowsnetworking.com/articles_tutorials/Windows_2003_Performance_Monitor.html) myself. It would be very difficult to attempt to diagnose and troubleshoot these problems remotely from these forums - do you have a network admin that you can ask to help
You say "It runs well if I manually run the job" - does it run well if you manually run the job under the same conditions (server, user credentials, time of day, etc.) when you experience poor behavior running it on schedule
This is the key to performance tuning - identifying the variables that are different between when "it works" and when "it doesn't work" and then figuring out how to use that information to make changes.
Good luck!