How to speed up DELETE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ada - 30 May 2008 16:09 GMT

Hi All,

We have a table with 300 mil records. We are planning to delete 100 million
of them on a production environment.
It's 24x7 db but we have a one time 8-hour OFFLINE window.
The existing night delete job (2 to 4 hrs) deletes by 500 chunks at a time,
causes no blocking but can hardly catch up the dailly added records (1mil a
day).

I am planning to increase chunks to a few hundred thousands for the duration
of the one time OFFLINE window.
I do not think setting recovery model to SIMPLE will speed it up.

What do you think? Any other ideas?

Thanks,

Ada

Signature

SQL Server DBA

Reply to this Message

Rick Sawtell - 30 May 2008 16:26 GMT

> Hi All,
>
[quoted text clipped - 18 lines]
>
> Ada

SIMPLE mode will not help unless you continue to chunk it. The deletes
will still be logged as a transaction (even in simple mode).

1. Back up database
2. SELECT * INTO NewTable
FROM OriginalTable
WHERE -- the 200 million you want to keep.
ORDER BY (clustered index column(s))
3. DROP OriginalTable
4. Rename NewTable to OriginalTable
5. Rebuild indexes.

We have used this strategy successfully many times.

Rick Sawtell

Reply to this Message

Ada - 30 May 2008 16:47 GMT

I used that strategy too, but creating the clustered index on the new table
will at least take 6 hrs, and there are three non-clustered indexes as well.
Indexes have to be created within the window.

But, it's a good option if they can extend the window.

Thanks,

Ada

Signature

SQL Server DBA

> > Hi All,
> >
[quoted text clipped - 34 lines]
>
> Rick Sawtell

Reply to this Message

Rick Sawtell - 30 May 2008 16:56 GMT

> I used that strategy too, but creating the clustered index on the new
> table
[quoted text clipped - 3 lines]
>
> But, it's a good option if they can extend the window.

How wide is your clustered index that it takes 6 hours to create?
Keep in mind also that the data is already going to be sorted in clustered
index order, that should speed up the index creation process.

Rick Sawtell

Reply to this Message

Eric Isaacs - 30 May 2008 19:44 GMT

Test it before the window to see how long it would take. You could do
a good portion of the work before the window (copying the other data
across, setting up the indexes, then when the window opens, just copy
across the changes (assuming you have a way to track the updated/added
records.)

Another option would be to delete the 100 million in time in smaller
batches. Pick them off when the usage is low with a sproc that runs
on a timer to drop a million at a time.

If you script everything out, even if it takes 6 hours to rebuild the
indexes, you can probably squeeze it in the 8 hour window???

Reply to this Message