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
- If you are deleting most of the table, use
SELECT INTOto 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
CHECKPOINTto 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.
There are some hint, but which version are you using? Is it enterprise edition? Anyway:
- If you can, move transaction log to a faster disk
- Analyze the where. Will it use an index to identify records to delete? If not, can you add an index?
- Do you have any index on the table that you can drop? If yes, drop them.
- Do you have foreign keys versus this table? These can really slow down your delete.
- If you have an enterprise edition and bottleneck is disk IO, a compression at row level, can give you a little help (or not, depending from your data)
- Can you partition the table? Local indexes and drop of partitions can be faster.
- Investigate where is bottleneck via activity monitor.
Add details, when you work with big database there is not a single valid answer.
You should try deleting them chunk by chunk, probably deleting in loop, each delete iteration it's own transaction and then clearing the log at the end of each loop iteration.
Also, you would need to find the number that you are going to use as value in chunk for deleting the records. It requires a thorough testing, would be better if you can test for the chunk value in UAT first.
On how to proceed, would refer you to Break large delete operations into chunks
Adding few more points...
- Try checking if the predicate has index on it and also see stats.
- If you are deleting large number of rows and you also don't want temp table option. Go for
- See if you have any triggers, specifically after delete triggers.
To get more help, post the query you are using, table info plus any blocking info.