19

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.

Improve this question
6
  • 3
    1000 rows per minute sounds extremely slow. Are you experiencing blocking? Or is it as slow to select the rows too, which would suggest a need for index?
    – James Z
    Commented May 27, 2015 at 15:07
  • Probably you have to create index to cover your delete criteria.
    – Ginden
    Commented May 27, 2015 at 15:08
  • 7
    There are not enough details to provide an answer. Which query do you execute? Do you have indexes on the criteria columns involved (if any)? Do you have triggers on delete? ...
    – Sébastien Sevrin
    Commented May 27, 2015 at 15:10
  • 3
    Are you trying to delete a billion rows at a time? Is it possible that you are waiting for autogrow after autogrow after autogrow? (It's more than likely that it is log activity you are waiting on, not the actual delete activity.) See this article...
    – Aaron Bertrand
    Commented May 27, 2015 at 15:16
  • 3
    Also. Any foreign key constraints? Please provide complete table definition, query, and execution plan.
    – Martin Smith
    Commented May 27, 2015 at 20:31

5 Answers 5

Reset to default
30

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:

  1. 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).
  2. If you are deleting the whole table, use TRUNCATE or DROP/CREATE.
  3. 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.)
  4. 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.
Improve this answer
4
  • +1, from me for an excellent article. This has helped me in past to make our developers understand the delete operation when they just keep reaching us for slowness and growth in log file.
    – KASQLDBA
    Commented May 27, 2015 at 15:23
  • Also, if there are any unnecessary indexes, dropping them will increase the delete speed. Again if deleting all or almost all data, dropping all indexes first and creating them again afterwards may have a nice impact.
    – Tony Hinkle
    Commented May 27, 2015 at 15:32
  • 3
    @Tony dropping the index has to be logged too (as does creating it), so it may just be a matter of when you want to pay that cost. Without testing, I'm not convinced there is a huge advantage for the delete scenario (like there would be for insert/update), unless you have indexes you're not going to keep afterward.
    – Aaron Bertrand
    Commented May 27, 2015 at 15:38
  • Does temporary disabling of FK constraints can improve the query?
    – Lev Z
    Commented Dec 17, 2018 at 15:52
4

There are some hint, but which version are you using? Is it enterprise edition? Anyway:

  1. If you can, move transaction log to a faster disk
  2. Analyze the where. Will it use an index to identify records to delete? If not, can you add an index?
  3. Do you have any index on the table that you can drop? If yes, drop them.
  4. Do you have foreign keys versus this table? These can really slow down your delete.
  5. 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)
  6. Can you partition the table? Local indexes and drop of partitions can be faster.
  7. Investigate where is bottleneck via activity monitor.

Add details, when you work with big database there is not a single valid answer.

Improve this answer
1
  • 1
    for me difference from avg. 3 seconds per record deletion to 0.00 for thousands of records was foreign keys versus current table. the impact is unbelievable. saved my day
    – ahmad molaie
    Commented Dec 12, 2020 at 11:03
2

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

Improve this answer
0

delete can be slow if big table has recursive foreign key.

if so, find opportune time, disable dependend services, disable recursive foreign key, perform massive delete, then restore foreign key again.

Improve this answer
1
  • 1
    this was exactlly my case. It's a bit risky to disable contraint, but delete went from 1 row/seocnd to 500 /second
    – Jurion
    Commented Dec 3, 2019 at 22:23
0

Adding few more points...

  1. Try checking if the predicate has index on it and also see stats.
  2. If you are deleting large number of rows and you also don't want temp table option. Go for tablock option.
  3. 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.