MS SQL : The best way to delete rows from a ginormous table

Question

I have a very large table [X], which has 170 million rows, and we need to archive data to keep only used records in [X]. We are doing this to keep our system fast as it is slowing down. We are only using a small amount of rows from the whole table (speaking of less then 10%), so we can afford to archive a lot of data into for example Archive.[X].

The problem is that when we try to delete records, it takes a lot of time. Now we have run the following checks for troubleshooting to see any possibilities why it takes so long 1) The table is indexed 2) No un-indexed foreign keys 3) No triggers doing extra work in the background on delete

Have any of you ever encountered a similar scenario? What is the best procedure to follow when doing something similar? And are there any tools out there that can help?

I appreciate your help!

Answer 1

Options

• Why not take the 10% into a new table?
• Batch delete/insert not in a transaction (see below)
• Partition table (aka let the engine deal with it)

To populate an archive table

SELECT 'starting' -- sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (50000) dbo.Mytable
    OUTPUT DELETED.* INTO ArchiveTable 
    WHERE SomeCol < <Afilter>

    -- maybe CHECKPOINT

    WAIT FOR DELAY ...
END