|
|
Posted by Greg D. Moore \(Strider\) on 05/11/06 04:10
"sql_server_user" <kaioptera@gmail.com> wrote in message
news:1147299426.561430.312200@u72g2000cwu.googlegroups.com...
> Thanks, Erland - I'm gonna have to pay you a consulting fee soon.
>
BTW, might be worth looking at using BCP to dump it out and then insert it
into the new table.
Then build your indices.
> The recovery model is set to simple, there are no nonclustered indexes
> on the table, and I agree that clustering on insert is the way to go,
> since I think trying to recluster the new table with all the data in it
> would just blow up the logs and tempdb like my initial insert attempt.
> I like the idea of the batched insert with the low fill factor
> clustered index, however, I ran a bunch of tests and the amount of log
> and tempdb space required seems to be a function of the size of the
> destination table, not the number of rows in the batch: I would insert
> a batch (10 million rows), shrink the log and tempdb, insert another
> batch, and so on, and every time, the log and tempdb would require more
> space, roughly linearly as my very approximate data below indicate:
>
> after 1st batch: log = 1GB, tempdb = 2GB
> after shrinking and 2nd batch: log = 2GB, tempdb = 4GB
> after shrinking and 3rd batch: log = 3 GB, tempdb = 6GB
>
> So I'm worried about the batch approach for the full insert.
>
> I'm currently trying an SSIS copy from one table to the other, and
> since it's doing a bulk insert it doesn't seem to be touching the db
> log. However, tempdb is getting pretty fat, 12GB and going strong, but
> I've cleared a lot of room for it this time - it has about 179GB to go,
> so hopefully that will be enough. If this doesn't work I'll definitely
> try your batched insert suggestion.
>
> Thanks again,
> Seth
>
> Erland Sommarskog wrote:
> > sql_server_user (kaioptera@gmail.com) writes:
> > > I'm trying to copy all 440 million rows from one table in my SQL
Server
> > > 2005 db to another table with a different clustering scheme. After a
> > > few test inserts that were successful (up to a million rows in 20
> > > seconds with no problem), I crossed my fingers and went whole-hog with
> > > the naive "insert into dest_table (column1, column2, ...) select
> > > column1, column2, ... from source_table" but of course it ran out of
> > > space in *both* the db log file (at about 130 GB) and the tempdb log
> > > file (at about 54GB) and rolled it back, wrapping up 10 hours after I
> > > kicked it off.
> >
> > That operation is definitely not a walk in a park.
> >
> > I think I would attempt this strategy:
> >
> > 1) Set recovery for the database to simple recovery.
> > 2) Create the clustered index on the target table with a relatively low
> > fill factor, say 30%. (Of course, no NC indexes would be in place.)
> > 3) Insert batchwise from the old table, where batches are defined by
> > intervals of the clustered index of the source table. For batchsize,
> > I would use 100000 to 500000 rows, depending on wide the table is.
> > 4) I would make the control loop so that it is easy to stop it. For
> > instance on each iteration read from a control table. Possibly also
> > a table the defines the batches, so you can keep track of what has
> > been copied. But the table should not be joined with the INSERT;
> > read interval into variales.
> > 5) Occassionally stop the process and monitor fragmentation in
> > target table. It should first decrease, as holes are filled in,
> > but then it will increase again. (But you will probably see this
> > from the fact that the time for iteration increases.)
> > 6) Once everything is done, switch to full recovery and backup the
> > database.
> >
> > My thinking here is that creating the clustered index on this monster
> > is going require a lot of log and tempdb - you can guess what happens.
> > So let's have the clustered index in place from the start, even if
> > that will take longer time.
> >
> >
> >
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> >
> > Books Online for SQL Server 2005 at
> >
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
Navigation:
[Reply to this message]
|