December 28, 2010 at 8:27 am
I'm runing SQL 2005 64x on Windows 2003 64x. 4xquad core cpus. 128gb ram.
The following command has been running for 18 hours now.
SELECT * INTO localdb..local_tablename
FROM OPENQUERY(remoteServer, 'SELECT * FROM servertablename ')
I have been talking with the people who handle the remote server. It's an Oracle server. When I first started the query the remote server dba said that Oracle said the query had 11 hours to finish, extimated. We watched that number decrease over the 11 hours. Now the query says it has finished on the Oracle side.
The query is still running on my side. The table I am trying to pull has 112 million rows in it and occupies 13gb on the Oracle side. There are no indexes on the Oracle side, or I would have tried to pull it down in chunks.
I look at the Activity Monitor and the process is still going. The CPU cycles are moving in very small increments, but the Physical IO is moving at a higher rate.
Am I correct in assuming that my server has pulled down all the rows, the rows are in tempdb and are now being put into the selected database?
Aside from pulling this data down in chunks, what other way would you guys suggest me to do this? I have one more table I have to pull down and that table is about 200 million rows and 25gb.
Oh yea, and I have to have this done before 2011 starts. lol.
Thanks in advance for any response to this post.
December 28, 2010 at 8:47 am
It has been running for 18 hours total. This is from the time that I hit Execute. So I am guessing that it has been processing the rows for the last 7 hours.
December 28, 2010 at 9:38 am
I'd take a look at sys.dm_exec_requests and see what that process is doing. What's it waiting on? Is it blocked? All that stuff.
In general, yes, I'd break this down into smaller chunks to move it down. Or, maybe, break it down to pull it out of Oracle to a flat file using SSIS and then import from the flat file into SQL SErver. Direct access to Oracle, especially this big an access, can be very problematic through linked servers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 28, 2010 at 11:15 am
Here is the result of 5 snapshots of the relevant columns of sys.dm_exec_requests. I don't see any blocking. It just appears from what I can see that it's just working on something.
blocking_session_id wait_type wait_time wait_resource open_transaction_count open_resultset_count
------------------- ---------- ----------- ---------------- ---------------------- --------------------
0NULL02:1:575766921
0NULL02:1:575865121
0NULL02:1:575904321
0NULL02:1:575932121
0NULL02:1:575961121
percent_complete estimated_completion_time cpu_time total_elapsed_time writes logical_reads
------------------------ ------------------------- ----------- ------------------ -------------------- --------------------
0.003596656733612643151218477369334
0.003596656734110193152168477371722
0.003596656734307683152547477372708
0.003596656734448303152816477373459
0.003596656734721273153339477374755
December 28, 2010 at 11:19 am
It's running then. No other way to be sure what it's doing. You can use the offset to understand which statement is currently being run, assuming that's useful. Otherwise, all you can do is look at the table to determine how many rows have been moved.
Also, one thing that could be slowing stuff down, did you grow the db prior to running this? If not, it's possibly slowing down while it grows the database over & over.
That's about all I can think of. If it's a long running query and it's not hung or blocked, it's just going to run for a while.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 28, 2010 at 12:12 pm
Only one statement in the batch.
I added about 10gb to the database about 2hrs before this statement started. I'm assuming that's enough time for the lazywriter to 0 out the space.
The database is set Autogrow off on log and data files.
December 28, 2010 at 5:52 pm
Why don't you use SSIS to move the data? I can move 160 M rows using the Attunity drivers between 1 to 3 hours.
December 29, 2010 at 7:41 am
The should be dumped to a flat file and bulk inserted in bulk logged mode.
Hope this helps.
December 29, 2010 at 12:56 pm
If I'm curious as to what's happening on this type of activity I usually just do:
SELECT COUNT(*) FROM Table WITH (NOLOCK)
You get an approximation of what's going on.
Like other posters said, I'd go with export to flat file from Oracle and Bulk Insert in bulk logged mode.
Todd Fifield
January 26, 2011 at 7:01 am
It turns out that using SSIS to a flat file solved my problem. There was something happening in the background in SQL server that was hung up. The statement was finished on the remote server and had already transferred all of the data to my server. After waiting a day for my server to finish processing the data, I killed the process. I ran the exact same statement through SSIS, exporting to a file, then bcp'ed it in with a batch of 10k rows and everything ran fine.
My guess is that SQL server was getting hung up because it was trying to do one big insert from temp space.
My problem is fixed now, thank you to all who have responded.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply