Delete large number of rows help

  • I have a batch process where i have to delete around 75 MIL rows out of 1.3 Billion rows table.  And then insert the new rows back. I have been told that there is no way to do incremental load on these. I believe the DELETE operation is taking very long (2-3 hours). Database is in Simple Recovery model. Any ideas how to speed up this delete? I would sincerely appreciate it.


  • Are the rows all at the "end" of the Clustered Index or are they scattered throughout?


    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Scattered throughout.

  • That's about 5% of the table, so it's unlikely that an index will help tons. However, have you looked at the execution plan for the DELETE statement? How is it satisfying the query?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Have you got a clustered index on the table? You can do a single table scan and insert the values of the clustered index key that need to be deleted into a temporary table  then use the temporary table to join to the table and delete rows in multiple batches.


  • DELETE in batches rather than all at once, as long as each DELETE won't require (up to) a full scan of the table to find the rows to delete.

    If you need to delete all rows at once, for whatever reason, be sure to pre-grow the log file to handle the DELETE.  Allowing the log file to grow dynamically during the DELETE instead will drastically slow down the DELETE.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Has the table in question been partitioned (or the clustered index)?

    If so, then swap out each partition to it's staging table, run the deletes/inserts, then swap back in.

    If not, learn about it here:

    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply