May 25, 2011 at 3:07 pm
I have a process running to insert roughly 600MM rows from one database to another, joining on a few tables along the way. It is still in the phase where it is gathering up/aggregating into tempdb, as it hasn't begun actually inserting anything thus far. I performed a very similar query against a larger table (~700MM rows) and, while it was joining against a few less tables, it did finish in about 6 hours. This one has been going for over a day now.
I knew that it was going to take a while to run, but I'm now being asked to provide an estimate as to its completion. For a few reasons that I'd rather not get into (I'm not looking for optimization help), I could not piecemeal this - it had to be run as a whole. Does anyone have any ideas on how I could find out its overall progress?
The query form is basically:
insert into table1(a,b,c,d,e)
select a,b,c,table3.d,table4.e
from table2
join table3 ...
join table 4 ...
May 25, 2011 at 3:11 pm
IIRC look in dm_exec_requests there's an estimate end time column in there. That column is populated for backups, restores, checkdb. I've never heard of it being populated for a query but that you be your best guess at the moment.
You could also watch for blocking, maybe it's just getting stuck somewhere.
The most likely problem is ressource contention and without a benchmark to compare to it's really hard to guesstimate the end time.
May 25, 2011 at 3:15 pm
Yeah, thanks. I was checking dm_exec_requests already and the ETC was 0. There's very little blocking going on, just an occasional IOCOMPLETION wait.
May 25, 2011 at 3:27 pm
The only thing I have in mind is to watch tempdb to make sure it doesn't run out of room. Because this db is probably getting hammered at the moment.
Check the default trace to see if it's autogrowing continually. Maybe you could start adding space there to stop waiting on san (if there's a real wait).
Other than that your crystal ball is just as good as mine.
I'm sure you're aware of this, but you can select from the target table with nolock to see when the insert actually starts. From there on out you can guestimate the end time. Untill then just make sure the server is not chocking and let her go!
May 25, 2011 at 3:30 pm
Thanks Ninja's. Yep, tempdb has plenty of room, and I'm keeping an eye on the dest. table, waiting for *anything* to show up.
May 25, 2011 at 3:34 pm
coronaride (5/25/2011)
Thanks Ninja's. Yep, tempdb has plenty of room, and I'm keeping an eye on the dest. table, waiting for *anything* to show up.
Just like I was thinking... you had a good grip on the situation... hoping for the miracle cure :w00t:.
May 25, 2011 at 3:40 pm
here's a stab in the dark - if i know that i started off with a minimum tempdb size and i'm joining all of one table with all of another table, would it be completely unreasonable to postulate that once tempdb got over the size of the sum of the size of those tables, it has to be pretty darned close? 🙂
May 25, 2011 at 3:56 pm
I'm not 100% sure about the internals on this. It "could" be a way but then again how do you figure that it's not some other query or ETL causing this or participating in the pool?
Again for this to possibly work, you'd need your normal benchmarks or to be alone working on that server.
May 25, 2011 at 3:59 pm
I'm the only one on this server. 😀
May 25, 2011 at 4:00 pm
coronaride (5/25/2011)
I'm the only one on this server. 😀
Hopefully an internals guru will see this and provide more hints of what you could do... right now that's beyond me.
May 25, 2011 at 4:55 pm
coronaride (5/25/2011)
here's a stab in the dark - if i know that i started off with a minimum tempdb size and i'm joining all of one table with all of another table, would it be completely unreasonable to postulate that once tempdb got over the size of the sum of the size of those tables, it has to be pretty darned close? 🙂
Nope. Would depend on structure.
If you've got a key table with 3 ints and then a lookup table with 20 500 byte rows.... the sum isn't going to do you any good. Multiply them by each other and you'd be closer.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply