Posted by Erland Sommarskog on 03/22/06 00:03

hallpa1@yahoo.com (hallpa1@yahoo.com) writes:
> I have been testing heavily on a full copy of the production data.
> Unfortunately the test server that I was given is a much slower machine
> than the production box.

That is probably a good thing. :-) At least, you will not get estimates
that are overly optimistic.

> In my test environment, doing the purge without the indexes is much
> faster than purging with the indexes in place. However, it took over
> 17 hours to rebuild the indexes afterward.

That's indeed a long time.

> My estimates suggest that purging without the indexes then rebuilding
> them will take about 40 straight hours. If I try to do the purge with
> the indexes in place, it will take about 200 hours.

Hm, unless you have some extra non-business days around Easter you can
use, it sounds like a purge bit by bit with the indexes in place.

Of course, if you can get a window from Friday night to Monday morning,
that will suffice, but it will be a little nervous. Then again,
indexes can always be recreated by restoring a backup, but then all
purge job would be lost.

Stu's suggestion to go by clustered index is a good one, but I recall
that only one of your tables had one.



--
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

[Back to original message]