November 30, 2006 at 7:02 am
I've got a query with which I'm trying to insert 42 million records into another table (the target table is indexed).
For the last 18 or so hours the query has been suspeded when I look at it with Activity Monitor, however I'm seeing the log file grow and the WaitType (PageIOLatch_EX) continually changes when you refresh it as to which particular resource is being targeted.
Can I reasonably assume that the query itself is actually still running, it's just bogged down trying to deal with the indexes on the target table and therefore that's quite probably the reason for the insane delay? Or atypically am I better off killing the process and segmenting my base 42 million record table into many smaller and possibly more manageable segments for the insert process?
November 30, 2006 at 10:19 am
do an sp_who2 every few seconds and you will see it running at some point
most likely you have I/O roadblock and it's waiting on I/O to finish before going forward
November 30, 2006 at 11:39 am
When I run the SP_WHO2, my task is staying in suspended mode still.
The first thing I see is that the command "CHECKPOINT" has a CPUTime of 400500 and a DiskIO of 16545. (SPID 10)
The next piece I see is for SPID 58 (which is the insert statement that I am running through SSMS) has a command of INSERT with CPUTIME of 11135137 and DISKIO of 38295871.
I'd assume it's a disk IO thing as well, especially since the log file for this particular transaction is at or about 97 GB currently (it started at about 5 GB before I ran this query). I've still got disk space left so that's not an issue just yet, but I'm wondering if the task is basically dead behind the scenes and I should look at rolling it back, or if it's still going forward, just very slowly.
Note : This query has been running since Monday night at 5:00, so I've given it what I would have hoped was plenty of time to complete.
November 30, 2006 at 2:45 pm
if you do a rollback, it will probably take a while
what kind of hardware are you running on? we have a test environment for sql 2005 on some ancient fiber switches and our production is on some 6 year old fiber switches and going to an old EMC SAN. And we get this in both environments.
December 1, 2006 at 6:47 am
Heh this is all on a local machine.
Athlon X2 4600+ CPU.
3GB of RAM (1.75 GB dedicated to SQL)
Serial ATA HD's (300 MB/S) all on their own controller.
Indexes on 1 drive.
Data on another
TembDB on a 3rd.
And yeah, I'm getting ready to begin the roll back I guess, which stinks. But such is life. A query shouldn't take a week, even as large as this one is.
December 1, 2006 at 7:43 am
where is the log?
we have a high end HP server with storage on a SAN and updates that affect 300 million rows take a few days to complete. i wouldn't bother with a rollback since it will take days to complete as well and you are better off waiting for the query to complete.
December 1, 2006 at 11:52 am
It's already in a rollback.
And the log is on it's own SATA drive inside of the same machine.
My next plan is to simply segment off the 42M records and parse them down to months and import those one at a time. Probably be much easier than the 1 big import.
December 1, 2006 at 1:47 pm
One month or one week should be fine depends on the data size...
Make sure you have the index on time column and you have enough allocatted free space in db data and log file before starting your insert operation.
MohammedU
Microsoft SQL Server MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply