Improve delete speed for SQL Server

We have huge production database, its size is around 300GB. Is there any approach to improve performance of a delete query? Right now deletion speed is between 1-10k per minute, it is very slow for us.

If you are trying to delete a large number of rows in a single statement, then it is likely you are waiting on log activity. So you can:

• Make sure your log is adequately sized so that growth events don't slow you down. With the defaults your log is probably starting at 1MB with 10% growth. Growth events are expensive, and if you are logging even 10 GB of deletes, this will destroy performance not just now but also in the future (due to what this does to VLFs).

• If you are deleting the whole table, use TRUNCATE or DROP/CREATE.

• If you are deleting most of the table, use SELECT INTO to put the data you want to keep into another table, then TRUNCATE, then move the small portion back. (Or just drop the old table, rename the new, and re-apply constraints / permissions etc.)

• Minimize the impact of logging in the first place by deleting the data in chunks instead of all at once. See this article. You can also consider switching to simple recovery temporarily, so that you only have to CHECKPOINT to clear the log instead of take log backups, but you need to be sure to set it back and to take a new full backup to re-initiate the log chain.

StackExchange Database Administrators Q#102571, answer score: 29