During work hours, take a look at sys.dm_exec_requests and see what the wait_type column says. This will tell you what the requests are waiting for.
Right before working hours, you could run DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); and then look at sys.dm_os_wait_stats to see the accumulated stats during the day.
Do you have indexes defined on any of these tables? If not, then table scans might be clogging your I/O system, and locks might be causing some blocking.
If people are running SQL Profiler traces during work hours, those could be slowing down the entire system. You could detect those by quering sys.traces.
Running a careful profiler trace yourself might reveal some interesting facts about this query. Try capturing a Showplan XML Statistics Profile event when it runs. Maybe the query processor is choosing a very bad query plan. Maybe the plan is generating intermediate tables that have a ridiculous number of records, or maybe there is a bad nested loop that would work better with an index or with a merge join.
Since this is an external server, another possibility is that you are simply overwhelming network bandwidth during working hours.
You mentioned in a comment that there was a lot of data coming back. Adding some filters in the query might help-- if that is an acceptable solution for your application.
Seeing some comments saying not to use SSIS for this, but my vote would be for using SSIS based on the fact that you said that the data would be transferred between servers. If you used a stored proc, you would have to have a linked server setup.
And yes, using SSIS would allow you to run your extract and load on your tables in parallel. It would also give your more flexibility to do things like dump your extract to raw files to get that done quickly without putting as much pressure on your source system and then import the raw files on your destination server where it might take longer.
I also guess you could use replication, but if you don't have replication already installed and configured, that would be a bigger lift to get up and running then writing a simple SSIS package that could do all this for you, in my opinion.
Best Answer
There are a couple of ways to do this. It depends on your setup. As per the previous question, if you just need a copy of everything you can backup and restore the database onto a new instance or, on the same instance with a different name.
If you need to physically pipe data with identity columns, into a new database thats already setup, you could use the import export tool. With that you can easily select just the tables you want data from, and the destination. Its quick and easy for a one time load. If you need to keep Identity columns, remember to select Column Mappings, and check the box to enable Identity insert per the article on Microsoft's website.
This is just one option to moving the data. A quick google and you will find many other options, including BCP, Linked Servers, SSIS, etc. It all depends on how often you want to do this load. How often do you need to do this?