Posted by Erland Sommarskog on 03/18/06 00:43

(hallpa1@yahoo.com) writes:
> I am designing a purge process for a db that has grown to almost 200GB.
> My purge process will remove about 1/3 of the 500 million rows spread
> over seven tables. Currently there are about 35 indexes defined on
> those seven tables. My question is will there be a performance gain by
> dropping those indexes, doing my purge, and re-creating the indexes. I
> am afraid that leaving those indexes in place will create a lot of
> extra overhead in my delete statements by having to maintain the
> indexes. I know that it could take many hours to rebuild the indexes
> afterward, but I am planning on doing that anyway. The reason that I
> want to know whether I should drop the indexes ahead of time, is I may
> not be able to do the entire purge at once and the tables may need to
> be accessed between purges. If this occurs, I will need to have those
> indexes in place.
>
> So do I drop the indexes before the purge and re-create them later or
> do I leave them in place and re-index them afterward?

If you really want to know - benchmark. In any case, you should not
run such a heavy operation in prodction, before testing it on a copy
of the live data. In a test environment, you can try different strategies.

Of course, if there is a requirement that that the database is accessible
while you are doing your purge, you will have to find a low-impact purges
that delete fairly small slices at time, and in this case dropping index
out the question.

Again, this why running this in a test environment is important. If
you can say "I was able to run the purge, including restoring of
indexes that I dropped, in eight hours", then it may be deemed that
it is acceptable to take the database offline.

There is another factor to it. If you drop and recreate the indexes,
you will address fragmentation in the indexes, that your purge inevitably
will cause. Worrying here, though, is that only one table has a a clustered
index. This means that the data pages of the other six tables will
reamin fragmented. For this reason, I would recommend that once your
purge has completed, that you build clustered indexes on these tables.
I would also recommend that you keep them in place, but if your DBA thinks
they are bad, you can just drop them. In such case, drop it before you
recreate the non-clustered indexes. (Else it is a costly operation to
drop clustered indexes.)


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