CODEWITHSUNDEEP
sqlsql-server-2008database-optimization
Mez
Mez

Reputation: 4726

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

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!

Upvotes: 2

Views: 2923

Answers (2)

gbn
gbn

Reputation: 432421

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

Upvotes: 4

Kangkan
Kangkan

Reputation: 15571

You should go for partitioning your database/table.

Upvotes: 2

Related Questions