Forum Discussion
Delete Statement slowness
Hello,
We are running a delete statement on database every weekend where it deletes rows based on the 16 where conditions. It has to pass 16 where conditions before delete more than millions row.
Since delete statement itself is resource consuming activity and takes time to delete more than a millions of data.
Is it something these many where conditions causing this issue? our DB environment is like :-
- SQL Server 2022 Ent Version with latest patch
- TempDB files added considering the number of vCPUS on VM, also TempDB is on separate disk
- NDF Files of secondary filegroups on separate disk for better throughput
- MaxDOP is set to default
- Data and log files are on separate disks.
- Weekly DB maintenance plan is in place.
regards,
Nitin
2 Replies
takes time to delete more than a millions of data.
Delete the data batch wise, e.g. always 10K rows instead of all in one run.
DELETE TOP (10000) FROM yourTable WHERE ....Could you share your query plan? like Paste The Plan - Brent Ozar Unlimited®
