MSSQL Large Delete

Question

I have a table within a MSSQL database that contains more than 100 Million records spread over about 100 days worth of data. I need to purge some of this data based on its date which is an indexed field in the table. I've tried doing a DELETE FROM against an individual date but it was taking a long long time to execute and was causing a drop in server performance. Is there a better way of deleting such a large number of records? Some of this data is still required so unfortunately I can't use truncate.

Many Thanks Nick

Answer 1

I have had the best success when doing similar tasks with the following flow:

1. Copy the data to keep into a temporary table
2. Truncate the original table to purge all data
3. Move everything from the temporary table back into the original table

One major benefit of this is that your indexes will be rebuilt as you put the data back into the original table.

Answer 2

Well, if you were using SQL Server Partitioning, say based on the date column, you would have possibly switched out the partitions that are no longer required. A consideration for a future implementation perhaps.

I think your only option may be to delete the data in smaller batches, rather than in one hit, so as to avoid any potential blocking issues.

Answer 3

You could DROP all the indexes on the table, DELETE FROM the table, then re-CREATE the indexes. This could speed things up, but it depends on the percentage of records which are not deleted.

Answer 4

You could delete smaller chunks. Instead of say, a weeks worth try going for just a day. If that's too much try going for just an hour at a time.

Answer 5

Another option would be to select into another table just the data you wanted. You could set up partitioning on date this way.

If the date index was the clustered index, the deletes should happen faster as they would all be close together on the disk.

Answer 6

I do not like or advocate the temp table suggestion mentioned. If the server would happen to go down between the delete step and the insert step, you'd lose the data.

I'd be more likely to go with one of the following:

BCP the data you want to keep (BCP using QUERYOUT), truncate, re-import. Same effect as the temp table with less overall risk.

Copy the data to another permanent table, either in the same database or a different one, and then pull it back.

Delete in batches using the SET ROWCOUNT technique. If you're careful and crafty, you can loop this so it happens out of scope to the loop, so the deletes are independently committed. Unless your clustered index is related to how you're deleting data, this will cause extensive table fragmentation.

Answer 7

Probably, in some high-volume OLTP databases it is better not to delete data at all. Developers can create "IsDeleted" field or something like that. But this is a consideration for the future.

As for answer you accepted. I don't believe that it will work faster then simple DELETE approach, if you will copy 100Mb of data. It will be very heavy load and big transaction log growth. Generally, it depends on how much of that data would you like to remain untouched after delete will be finished.

What I would recommend is

1) If you can run your query in non-active hours, you should issue exclusive table lock and then delete records. this will save time SQL server will spend to propagate locks to many individual rows

2) if 1st approach is not possible, then delete by chunks, I will agree with John Sansom. Problems begins when there is a very large transaction that blocks lot of other active users transactions... So you have to make delete in small portions, each in its own transaction...

3) you could also temporary switch off (or drop and then recreate) Before/After Delete triggers and constrains (including foreign keys) however there is an integrity risk and this approach require some experiments.

AFAIK, disabling/enabling indexes will not improve the situation because when you delete records, there will be "holes" in index trees... So this may affect the performance of the next SQL queries for the same table, and sooner or later you may want to rebuild the indexes, however I never see any effect on how indexes (even when you have too may indexes) may decrease the speed of delete operation

In most cases bad performance of DELETE is when indexes is not used by DELETE query (you may check query plan) or when you have too many foreign keys or heavy triggers logic.

Answer 8

Delete in chunks.

Make a delete based on a select according to your criteria, but the select has a TOP 100000 - so only 100000 rows get deleted on every call. Call until no more is deleted.