Force sp_post_xpload (DBCC REINDEX) to use more engines?

[pow43]

Hi,

We are planning to migrate a 260 Gb database from Solarsi to Linux/
SLES 11 on a virtual server (vmvare). First migration attempts shows
that the load itself uses approx. 2 hours but the following
sp_post_xpload (DBCC REINDEX) uses approx. 30 hours!! This is far to
long time for the system to be down and not accessable. When the
procedure is running I see that just 1 of total 4 engines. The one
that i being used is running 100% constantly. Is it possible to force
it to use more engines? I have read about enginegroupes, but not shure
how to use it and assigne it to this prosess. I tried to assign one
enginegroup with 3 engines to isql which I executed the sp_post_xpload
from, but this did not change anything. This was done after the
procedure was started/running.

Any suggestion how to speed up this prosess either with more engines
or other tricks are highly appreciated.

-Pow

[b***@msn.com]

Post by pow43
Hi,
We are planning to migrate a 260 Gb database from Solarsi to Linux/
SLES 11 on a virtual server (vmvare). First migration attempts shows
that the load itself uses approx. 2 hours but the following
sp_post_xpload (DBCC REINDEX) uses approx. 30 hours!! This is far to
long time for the system to be down and not accessable. When the
procedure is running I see that just 1 of total 4 engines. The one
that i being used is running 100% constantly. Is it possible to force
it to use more engines? I have read about enginegroupes, but not shure
how to use it and assigne it to this prosess. I tried to assign one
enginegroup with 3 engines to isql which I executed the sp_post_xpload
from, but this did not change anything. This was done after the
procedure was started/running.
Any suggestion how to speed up this prosess either with more engines
or other tricks are highly appreciated.
-Pow

Engine groups wont help here. They allow similar connections
to be limited to running on a subset of engines; they don't allow the
work of a single process to be spread over multiple engines.
Worker processes do that, but still don't address the issue of
wanting the stored procedure to execute on more than one table
at a time.

You could drop indexes prior to the migration and use multiple
sessions running CREATE INDEX commands to rebuild them
rather than use sp_post_xpload.

[Antony]

We have done the same migration many times. You can't really use sp_post_xpload on databases over 10-20Gb as it's too slow (I'm not sure why, but it is). As Bret says, best to get a DDL script to drop all indexes except APL clustered and rebuild them on the Linux serer. You can also then make it 3 or 4 scripts to run concurrently.

Remember also you'll need to bcp out/in any tables with TEXT columns as these will likely be corrupted (the documentation isn't really clear on that point - we found out the hard way :)