September 21, 2005 at 8:10 am
I have a new DTS package that imports from 1 to 3 million rows per day from a live system to a datastore.
If i run this by hand from my desktop it works pretty well and completes in 2 to 4 minutes (excluding the time to complete the indexing but that does not affect the production system).
If I run it from the server box it runs at about 1/30th the speed which will not work since it would require two hours to query the live system -
I can not schedule the job since it would then run from the server at the slower speed.
Does anyone know typically what the system differences may be that could cause this? I am not sure where to look
Thanks
RJ
September 21, 2005 at 8:37 am
Servers usually give a higher priority to background server tasks. Workstations always give higher priority to foreground applications. Have you attempted to schedule this package on the server and see if performance improves?
Another thing to look at may be the way your connections are defined within the package. I would take a close look at server names, authentication, etc... are both your server and workstation using the same DNS and WINS servers? Does your workstation have a hosts. or lmhosts. file that is different than the one on the server?
There certainly are many different variables that can affect performance when dealing with remote systems. My guess is that your server is not some old Pentium II system with a 10K pagefile set up on the system partition... so I have pretty much ignored the way to obvious hardware troubleshooting area.
Since you are dealing with remote servers.. is your server by chance multi-homed on more than LAN? if the default route/path to the remote server(s) is over a VPN or Dial-up connection (smile).. that could certainly cause a performance hit like this...
Hope some of these ideas give you some additional ideas to check for.
-Mike Gercevich
September 21, 2005 at 10:37 am
Another thing to consider is SS2K DTS is redistributable, so the components can be put on any workstation/server and run from there with no license issues. Not the same in 2005, but you could setup some other workstation or W2K server (not SQL) and put DTS there and schedule it to run from the OS (DTSRun) and forget about it.
September 22, 2005 at 8:52 am
Is the server a multiprocessor box? One of my colleagues experienced an issue with the max degree of parallelism setting on a multiprocessor server in which the parallel query plan on the server was actually less efficient than the query plan on his single-processor laptop (20 minutes on the laptop vs. 8 hours on the server). I don't know whether his situation is similar to yours or not in terms of the client-server set-up (nor am I a SQL Server dba - just a lowly DTS developer!), but it might be something to check. He says the max degree of parallelism can be set using a SQL query hint as well as on the server.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply