stored procedure. How to quickly delete a large number of records by criterion, if delete is blunt?

Question

Transact SQL

There is a stored procedure that cleans old data from tables once a week. Delete greatly reduces performance (several hundred thousand records need to be deleted), truncate cannot be used either. The question is, are there any tricks in this regard, such as bulk for example (it seems to me it would be suitable)? Can take a thousand records in delete and loop? The customer still has old Win Server 2003, which worsens the process even more. I really hope for your help)

Answer 1

Well, you will make a long query by looping even longer. Might be worth deleting more often? once a day, for example, at night.

Answer 2

How many records do you have in the table?
Are the fields that are used to select records for deletion indexed?
If you are deleting most of the data, it may make sense to transfer only the necessary data to a separate table, delete the old table, rename the new table to the old one, build the necessary indexes.

Answer 3

Thank you all very much. The issue was resolved by reindexing and looping.