September 12, 2008 at 6:47 am
I put together a package that has a Web task that makes a call
to a website to retrieve an xml file.
Enviornment specs:
Non-prod
Windows 2003 Enterprise sp2
SQL Server 2005 Developer Edition Sp1
Machine is running under Vmware and has 2gb of Memory.
Production Enviornment (at Colo facility)
Windows 2003 Standard Sp2
SQL Server 2005 Standard Edition Sp1
has 4gb of Memory. 2 Cpu's Dual Cored.
When I run this package on the non-prod server it completes in
2-5 minutes. It is returning a 36mb file.
When I run this on the Production Enviornment it takes anywhere
from 45-60 minutes.
I've looked at the System while this is running and I see no
cpu load to speak of (2-3%) and Memory is plentiful (over 2gb
free).
I've pinged the server that is hosting the Web Service and see
Latency of 56-80ms, which I don't think is significant.
Before I go to the third party hosting the web service I wanted
to make sure that there was nothing at a package level that
might be amiss.
Thanks for any help.
September 12, 2008 at 7:38 am
I would start by logging all of the events in the package to see exactly where it is getting "stuck".
The hardware differences are significant and could easily be the difference. Along with the production hardware being much better than your development environment, VMWare adds a software virtualization layer to everything that adds overhead to every process. Usually it is not a lot, but there are some things that is can slow down quite a bit. If you have multiple virtual machines on this physical server, NIC contention is one of thse areas so you may find that you are simply getting poor bandwidth getting the file. The file is pretty small so it is unlikely causing the process to take 5 times as long, but it is something to look at.
September 12, 2008 at 8:48 am
Michael Earl (9/12/2008)
I would start by logging all of the events in the package to see exactly where it is getting "stuck".The hardware differences are significant and could easily be the difference. Along with the production hardware being much better than your development environment, VMWare adds a software virtualization layer to everything that adds overhead to every process. Usually it is not a lot, but there are some things that is can slow down quite a bit. If you have multiple virtual machines on this physical server, NIC contention is one of thse areas so you may find that you are simply getting poor bandwidth getting the file. The file is pretty small so it is unlikely causing the process to take 5 times as long, but it is something to look at.
Thanks for the response. I did add logging to the Package for all events and I see the begin of the web service call, and the next event logged is when it completes 40-45 minutes later.
Note: The Non-Production Server VM is running in 5 minutes, it is
the physical box that is Production. So If VMware host can run in 5
why would a much beefier box take 9 times as long.
At first I thought maybe there might be a difference between developer and standard SQL 2005 edition, so on the prod box i just created a simple package that would only do the web call. It still took the 45 minutes to complete without any errors being logged.
September 12, 2008 at 9:21 am
Sorry, I did not read your post very clearly, I though your prod server time was in seconds.
Do you have access to PerfMon on the production server? It would be good to trace what is happening during that 45 minute period. It would also be good to try the web service without SSIS.
As far as SSIS, the Dev/Enterprise versions of SQL will act the same way, but make sure you are running the same service pack. Also check for differences in virus software.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply